Professional Web Applications Themes

SELECT DISTINCT + ORDER BY gives ERROR 145: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. - Microsoft SQL / MS SQL Server

Following is my stored procedure. If I take the DISTINCT out then everything works fine. BUT I need the distinct because it returns duplicate records. If I put the DISTINCT in then I get the following error: ERROR 145: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. CREATE PROCEDURE [sp_Fax_Select] sOrderBy varchar(50)='Id', iErrorCode int=-1 OUTPUT AS -- SELECT an existing row from the table. SELECT DISTINCT [Fax].Id AS Id, Fax.FaxID, Fax.ReceivedTime, Fax.Pages FROM [Fax] INNER JOIN [Order] ON [Order].FaxId = Fax.Id INNER JOIN Sender ON Sender.Id = Fax.SenderId INNER JOIN Prescription ON [Order].Id = ...

  1. #1

    Default SELECT DISTINCT + ORDER BY gives ERROR 145: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

    Following is my stored procedure. If I take the DISTINCT out then
    everything works fine. BUT I need the distinct because it returns
    duplicate records. If I put the DISTINCT in then I get the following
    error:
    ERROR 145: ORDER BY items must appear in the select list if SELECT
    DISTINCT is specified.


    CREATE PROCEDURE [sp_Fax_Select]
    sOrderBy varchar(50)='Id',
    iErrorCode int=-1 OUTPUT

    AS

    -- SELECT an existing row from the table.
    SELECT DISTINCT
    [Fax].Id AS Id,
    Fax.FaxID,
    Fax.ReceivedTime,
    Fax.Pages

    FROM [Fax]

    INNER JOIN [Order] ON [Order].FaxId = Fax.Id
    INNER JOIN Sender ON Sender.Id = Fax.SenderId
    INNER JOIN Prescription ON [Order].Id = Prescription.OrderId
    INNER JOIN Patient ON Patient.Id = [Order].PatientId


    ORDER BY
    CASE sOrderBy WHEN 'Id' THEN [Fax].Id ELSE Null END,
    CASE sOrderBy WHEN 'FaxID' THEN [Fax].FaxID ELSE Null END

    -- Get the Error Code for the statement just executed.
    SELECT iErrorCode=ERROR
    GO
    Dan Guest

  2. #2

    Default Re: SELECT DISTINCT + ORDER BY gives ERROR 145: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

    Put the result of you DISTINCT in a derived table and select an ordered set
    from that:

    SELECT [Id],
    FaxID,
    ReceivedTime,
    Pages
    FROM
    (
    SELECT DISTINCT
    [Fax].Id AS Id,
    Fax.FaxID,
    Fax.ReceivedTime,
    Fax.Pages

    FROM [Fax]

    INNER JOIN [Order] ON [Order].FaxId = Fax.Id
    INNER JOIN Sender ON Sender.Id = Fax.SenderId
    INNER JOIN Prescription ON [Order].Id = Prescription.OrderId
    INNER JOIN Patient ON Patient.Id = [Order].PatientId
    ) AS distinct_fax


    ORDER BY
    CASE sOrderBy WHEN 'Id' THEN Id ELSE Null END,
    CASE sOrderBy WHEN 'FaxID' THEN FaxID ELSE Null END



    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Dan" <danjbart> wrote in message
    news:fa556b4d.0304150511.6b545a25posting.google.c om...
    > Following is my stored procedure. If I take the DISTINCT out then
    > everything works fine. BUT I need the distinct because it returns
    > duplicate records. If I put the DISTINCT in then I get the following
    > error:
    > ERROR 145: ORDER BY items must appear in the select list if SELECT
    > DISTINCT is specified.
    >
    >
    > CREATE PROCEDURE [sp_Fax_Select]
    > sOrderBy varchar(50)='Id',
    > iErrorCode int=-1 OUTPUT
    >
    > AS
    >
    > -- SELECT an existing row from the table.
    > SELECT DISTINCT
    > [Fax].Id AS Id,
    > Fax.FaxID,
    > Fax.ReceivedTime,
    > Fax.Pages
    >
    > FROM [Fax]
    >
    > INNER JOIN [Order] ON [Order].FaxId = Fax.Id
    > INNER JOIN Sender ON Sender.Id = Fax.SenderId
    > INNER JOIN Prescription ON [Order].Id = Prescription.OrderId
    > INNER JOIN Patient ON Patient.Id = [Order].PatientId
    >
    >
    > ORDER BY
    > CASE sOrderBy WHEN 'Id' THEN [Fax].Id ELSE Null END,
    > CASE sOrderBy WHEN 'FaxID' THEN [Fax].FaxID ELSE Null END
    >
    > -- Get the Error Code for the statement just executed.
    > SELECT iErrorCode=ERROR
    > GO

    Jacco Schalkwijk Guest

  3. #3

    Default Re: SELECT DISTINCT + ORDER BY gives ERROR 145: ORDER BY items mustappear in the select list if SELECT DISTINCT is specified.

    Dan,

    The ORDER BY clause is a cursor operation. It acts on the result of the
    query. In ANSI SQL-92, one can only ORDER BY column names/expressions or
    aliases that are used in the selection list. The expression you are
    using is not an item in the selection list.

    Without the DISTINCT keyword, your query does not pose a problem,
    because SQL-Server will create an imaginary extra column which holds the
    ORDER BY expression. This is not done when the DISTINCT keyword is
    added, because this imaginary extra column could change the resultset
    (which by definition, an ORDER BY clause should not be able to do). So
    in that case, the ANSI SQL-92 rule is enforced, and an error is thrown.

    Tom, Steve and Jacco already posted very good solutions. Another option
    would be to add your ORDER BY expression as an extra column to the
    selection list.

    Hope this helps,
    Gert-Jan


    Dan wrote:
    >
    > Following is my stored procedure. If I take the DISTINCT out then
    > everything works fine. BUT I need the distinct because it returns
    > duplicate records. If I put the DISTINCT in then I get the following
    > error:
    > ERROR 145: ORDER BY items must appear in the select list if SELECT
    > DISTINCT is specified.
    >
    > CREATE PROCEDURE [sp_Fax_Select]
    > sOrderBy varchar(50)='Id',
    > iErrorCode int=-1 OUTPUT
    >
    > AS
    >
    > -- SELECT an existing row from the table.
    > SELECT DISTINCT
    > [Fax].Id AS Id,
    > Fax.FaxID,
    > Fax.ReceivedTime,
    > Fax.Pages
    >
    > FROM [Fax]
    >
    > INNER JOIN [Order] ON [Order].FaxId = Fax.Id
    > INNER JOIN Sender ON Sender.Id = Fax.SenderId
    > INNER JOIN Prescription ON [Order].Id = Prescription.OrderId
    > INNER JOIN Patient ON Patient.Id = [Order].PatientId
    >
    > ORDER BY
    > CASE sOrderBy WHEN 'Id' THEN [Fax].Id ELSE Null END,
    > CASE sOrderBy WHEN 'FaxID' THEN [Fax].FaxID ELSE Null END
    >
    > -- Get the Error Code for the statement just executed.
    > SELECT iErrorCode=ERROR
    > GO
    Gert-Jan Strik Guest

  4. #4

    Default Re: SELECT DISTINCT + ORDER BY gives ERROR 145: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

    If you use "group by " in stead of "distinct" it works fiine. (I also named
    the sp usp_* because sp_* procedures are first being searched for in
    master-db)

    CREATE PROCEDURE [usp_Fax_Select]
    sOrderBy varchar(50)='Id',
    iErrorCode int=-1 OUTPUT

    AS

    -- SELECT an existing row from the table.
    SELECT -- DISTINCT
    Fax.Id AS Id,
    Fax.FaxID,
    Fax.ReceivedTime,
    Fax.Pages

    FROM Fax

    INNER JOIN [Order] ON [Order].FaxId = Fax.Id
    INNER JOIN Sender ON Sender.Id = Fax.SenderId
    INNER JOIN Prescription ON [Order].Id = Prescription.OrderId
    INNER JOIN Patient ON Patient.Id = [Order].PatientId

    group by Fax.Id ,
    Fax.FaxID,
    Fax.ReceivedTime,
    Fax.Pages

    ORDER BY
    CASE sOrderBy WHEN 'Id' THEN Fax.Id ELSE Null END,
    CASE sOrderBy WHEN 'FaxID' THEN Fax.FaxID ELSE Null END

    -- Get the Error Code for the statement just executed.
    SELECT iErrorCode=ERROR
    GO

    jobi
    "Dan" <danjbart> wrote in message
    news:fa556b4d.0304150511.6b545a25posting.google.c om...
    > Following is my stored procedure. If I take the DISTINCT out then
    > everything works fine. BUT I need the distinct because it returns
    > duplicate records. If I put the DISTINCT in then I get the following
    > error:
    > ERROR 145: ORDER BY items must appear in the select list if SELECT
    > DISTINCT is specified.
    >
    >
    > CREATE PROCEDURE [sp_Fax_Select]
    > sOrderBy varchar(50)='Id',
    > iErrorCode int=-1 OUTPUT
    >
    > AS
    >
    > -- SELECT an existing row from the table.
    > SELECT DISTINCT
    > [Fax].Id AS Id,
    > Fax.FaxID,
    > Fax.ReceivedTime,
    > Fax.Pages
    >
    > FROM [Fax]
    >
    > INNER JOIN [Order] ON [Order].FaxId = Fax.Id
    > INNER JOIN Sender ON Sender.Id = Fax.SenderId
    > INNER JOIN Prescription ON [Order].Id = Prescription.OrderId
    > INNER JOIN Patient ON Patient.Id = [Order].PatientId
    >
    >
    > ORDER BY
    > CASE sOrderBy WHEN 'Id' THEN [Fax].Id ELSE Null END,
    > CASE sOrderBy WHEN 'FaxID' THEN [Fax].FaxID ELSE Null END
    >
    > -- Get the Error Code for the statement just executed.
    > SELECT iErrorCode=ERROR
    > GO

    Jobi Guest

Similar Threads

  1. Select a list of items into an aliased field when doinga select
    By ehaemmerle in forum Coldfusion Database Access
    Replies: 3
    Last Post: March 18th, 10:49 PM
  2. Using SELECT DISTINCT
    By The Ox in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 16th, 10:18 PM
  3. SELECT DISTINCT from two tables
    By Marco Alting in forum ASP
    Replies: 4
    Last Post: July 31st, 10:26 AM
  4. Select Distinct
    By Scott in forum Microsoft SQL / MS SQL Server
    Replies: 7
    Last Post: July 3rd, 05:22 PM
  5. Replies: 0
    Last Post: April 15th, 01:22 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139