ORDER BY items must appear in the select list if SELECT DISTINCT is specified

Question

I added the columns in the select list to the order by list  but it is still giving me the error   ORDER BY items must appear in the select list if SELECT DISTINCT is specified   Here is the stored proc   CREATE PROCEDURE  dbo   GetRadioServiceCodesINGroup    RadioServiceGroup nvarchar 1000    NULL AS BEGIN SET NOCOUNT ON   SELECT DISTINCT rsc RadioServiceCodeId                  rsc RadioServiceCode     -     rsc RadioService as RadioService FROM sbi l radioservicecodes rsc INNER JOIN sbi l radioservicecodegroups rscg  ON rsc radioservicecodeid   rscg radioservicecodeid WHERE rscg radioservicegroupid IN   select val from dbo fnParseArray  RadioServiceGroup       OR  RadioServiceGroup IS NULL   ORDER BY rsc RadioServiceCode rsc RadioServiceCodeId rsc RadioService  END

User · Answer

While they are not the same thing  in one sense DISTINCT implies a GROUP BY  because every DISTINCT could be re-written using GROUP BY instead  With that in mind  it doesn t make sense to order by something that s not in the aggregate group     For example  if you have a table like this    col1  col2 ----  ----  1     1  1     2  2     1  2     2  2     3  3     1   and then try to query it like this   SELECT DISTINCT col1 FROM  table  WHERE col2  gt  2 ORDER BY col1  col2   That would make no sense  because there could end up being multiple col2 values per row  Which one should it use for the order  Of course  in this query you know the results wouldn t be that way  but the database server can t know that in advance   Now  your case is a little different  You included all the columns from the order by clause in the select clause  and therefore it would seem at first glance that they were all grouped  However  some of those columns were included in a calculated field   When you do that in combination with distinct  the distinct directive can only be applied to the final results of the calculation  it doesn t know anything about the source of the calculation any more   This means the server doesn t really know it can count on those columns any more   It knows that they were used  but it doesn t know if the calculation operation might cause an effect similar to my first simple example above   So now you need to do something else to tell the server that the columns are okay to use for ordering  There are several ways to do that  but this approach should work okay   SELECT rsc RadioServiceCodeId              rsc RadioServiceCode     -     rsc RadioService as RadioService FROM sbi l radioservicecodes rsc INNER JOIN sbi l radioservicecodegroups rscg      ON rsc radioservicecodeid   rscg radioservicecodeid WHERE rscg radioservicegroupid IN       SELECT val FROM dbo fnParseArray  RadioServiceGroup           OR  RadioServiceGroup IS NULL   GROUP BY rsc RadioServiceCode rsc RadioServiceCodeId rsc RadioService ORDER BY rsc RadioServiceCode rsc RadioServiceCodeId rsc RadioService

User · Answer

Distinct and Group By generally do the same kind of thing  for different purposes     They both create a  working  table in memory based on the columns being Grouped on   or selected in the Select Distinct clause  - and then populate that working table as the query reads data   adding a new  row  only when the values indicate the need to do so      The only difference is that in the Group By there are additional  columns  in the working table for any calculated aggregate fields  like Sum    Count     Avg    etc   that need to updated for each original row read   Distinct doesn t have to do this    In the special case where you Group By only to get distinct values   And there are no aggregate columns in output   then it is probably exactly the same query plan     It would be interesting to review the query execution plan for the two options and see what it did      Certainly Distinct is the way to go for readability if that is what you are doing  When your purpose is to eliminate duplicate rows  and you are not calculating any aggregate columns

User · Answer

Try this   ORDER BY 1  2   OR  ORDER BY rsc RadioServiceCodeId  rsc RadioServiceCode     -     rsc RadioService

User · Answer

Try one of these    Use column alias   ORDER BY RadioServiceCodeId RadioService Use column position   ORDER BY 1 2   You can only order by columns that actually appear in the result of the DISTINCT query - the underlying data isn t available for ordering on

User · Answer

While they are not the same thing  in one sense DISTINCT implies a GROUP BY  because every DISTINCT could be re-written using GROUP BY instead  With that in mind  it doesn t make sense to order by something that s not in the aggregate group     For example  if you have a table like this    col1  col2 ----  ----  1     1  1     2  2     1  2     2  2     3  3     1   and then try to query it like this   SELECT DISTINCT col1 FROM  table  WHERE col2  gt  2 ORDER BY col1  col2   That would make no sense  because there could end up being multiple col2 values per row  Which one should it use for the order  Of course  in this query you know the results wouldn t be that way  but the database server can t know that in advance   Now  your case is a little different  You included all the columns from the order by clause in the select clause  and therefore it would seem at first glance that they were all grouped  However  some of those columns were included in a calculated field   When you do that in combination with distinct  the distinct directive can only be applied to the final results of the calculation  it doesn t know anything about the source of the calculation any more   This means the server doesn t really know it can count on those columns any more   It knows that they were used  but it doesn t know if the calculation operation might cause an effect similar to my first simple example above   So now you need to do something else to tell the server that the columns are okay to use for ordering  There are several ways to do that  but this approach should work okay   SELECT rsc RadioServiceCodeId              rsc RadioServiceCode     -     rsc RadioService as RadioService FROM sbi l radioservicecodes rsc INNER JOIN sbi l radioservicecodegroups rscg      ON rsc radioservicecodeid   rscg radioservicecodeid WHERE rscg radioservicegroupid IN       SELECT val FROM dbo fnParseArray  RadioServiceGroup           OR  RadioServiceGroup IS NULL   GROUP BY rsc RadioServiceCode rsc RadioServiceCodeId rsc RadioService ORDER BY rsc RadioServiceCode rsc RadioServiceCodeId rsc RadioService

User · Answer

Try this   ORDER BY 1  2   OR  ORDER BY rsc RadioServiceCodeId  rsc RadioServiceCode     -     rsc RadioService

User · Answer

Distinct and Group By generally do the same kind of thing  for different purposes     They both create a  working  table in memory based on the columns being Grouped on   or selected in the Select Distinct clause  - and then populate that working table as the query reads data   adding a new  row  only when the values indicate the need to do so      The only difference is that in the Group By there are additional  columns  in the working table for any calculated aggregate fields  like Sum    Count     Avg    etc   that need to updated for each original row read   Distinct doesn t have to do this    In the special case where you Group By only to get distinct values   And there are no aggregate columns in output   then it is probably exactly the same query plan     It would be interesting to review the query execution plan for the two options and see what it did      Certainly Distinct is the way to go for readability if that is what you are doing  When your purpose is to eliminate duplicate rows  and you are not calculating any aggregate columns

User · Answer

When you define concatenation you need to use an ALIAS for the new column if you want to order on it combined with DISTINCT Some Ex with sql 2008  --this works       SELECT DISTINCT  c FirstName         c LastName  as FullName      from SalesLT Customer c      order by FullName  --this works too      SELECT DISTINCT  c FirstName         c LastName       from SalesLT Customer c      order by 1  -- this doesn t       SELECT DISTINCT  c FirstName         c LastName  as FullName      from SalesLT Customer c      order by c FirstName  c LastName  -- the problem the DISTINCT needs an order on the new concatenated column  here I order on the singular column -- this works      SELECT DISTINCT  c FirstName         c LastName           as FullName  CustomerID          from SalesLT Customer c   order by 1  CustomerID  -- this doesn t      SELECT DISTINCT  c FirstName         c LastName  as FullName       from SalesLT Customer c        order by 1  CustomerID

User · Answer

Distinct and Group By generally do the same kind of thing  for different purposes     They both create a  working  table in memory based on the columns being Grouped on   or selected in the Select Distinct clause  - and then populate that working table as the query reads data   adding a new  row  only when the values indicate the need to do so      The only difference is that in the Group By there are additional  columns  in the working table for any calculated aggregate fields  like Sum    Count     Avg    etc   that need to updated for each original row read   Distinct doesn t have to do this    In the special case where you Group By only to get distinct values   And there are no aggregate columns in output   then it is probably exactly the same query plan     It would be interesting to review the query execution plan for the two options and see what it did      Certainly Distinct is the way to go for readability if that is what you are doing  When your purpose is to eliminate duplicate rows  and you are not calculating any aggregate columns

User · Answer

Try one of these    Use column alias   ORDER BY RadioServiceCodeId RadioService Use column position   ORDER BY 1 2   You can only order by columns that actually appear in the result of the DISTINCT query - the underlying data isn t available for ordering on

User · Answer

When you define concatenation you need to use an ALIAS for the new column if you want to order on it combined with DISTINCT Some Ex with sql 2008  --this works       SELECT DISTINCT  c FirstName         c LastName  as FullName      from SalesLT Customer c      order by FullName  --this works too      SELECT DISTINCT  c FirstName         c LastName       from SalesLT Customer c      order by 1  -- this doesn t       SELECT DISTINCT  c FirstName         c LastName  as FullName      from SalesLT Customer c      order by c FirstName  c LastName  -- the problem the DISTINCT needs an order on the new concatenated column  here I order on the singular column -- this works      SELECT DISTINCT  c FirstName         c LastName           as FullName  CustomerID          from SalesLT Customer c   order by 1  CustomerID  -- this doesn t      SELECT DISTINCT  c FirstName         c LastName  as FullName       from SalesLT Customer c        order by 1  CustomerID

User · Answer

Try one of these    Use column alias   ORDER BY RadioServiceCodeId RadioService Use column position   ORDER BY 1 2   You can only order by columns that actually appear in the result of the DISTINCT query - the underlying data isn t available for ordering on

User · Answer

You could try a subquery   SELECT DISTINCT TEST   FROM       SELECT rsc RadioServiceCodeId          rsc RadioServiceCode     -     rsc RadioService as RadioService     FROM sbi l radioservicecodes rsc        INNER JOIN sbi l radioservicecodegroups rscg ON  rsc radioservicecodeid   rscg radioservicecodeid     WHERE rscg radioservicegroupid IN          select val from dbo fnParseArray  RadioServiceGroup               OR  RadioServiceGroup IS NULL       ORDER BY rsc RadioServiceCode rsc RadioServiceCodeId rsc RadioService   as TEST

User · Answer

While they are not the same thing  in one sense DISTINCT implies a GROUP BY  because every DISTINCT could be re-written using GROUP BY instead  With that in mind  it doesn t make sense to order by something that s not in the aggregate group     For example  if you have a table like this    col1  col2 ----  ----  1     1  1     2  2     1  2     2  2     3  3     1   and then try to query it like this   SELECT DISTINCT col1 FROM  table  WHERE col2  gt  2 ORDER BY col1  col2   That would make no sense  because there could end up being multiple col2 values per row  Which one should it use for the order  Of course  in this query you know the results wouldn t be that way  but the database server can t know that in advance   Now  your case is a little different  You included all the columns from the order by clause in the select clause  and therefore it would seem at first glance that they were all grouped  However  some of those columns were included in a calculated field   When you do that in combination with distinct  the distinct directive can only be applied to the final results of the calculation  it doesn t know anything about the source of the calculation any more   This means the server doesn t really know it can count on those columns any more   It knows that they were used  but it doesn t know if the calculation operation might cause an effect similar to my first simple example above   So now you need to do something else to tell the server that the columns are okay to use for ordering  There are several ways to do that  but this approach should work okay   SELECT rsc RadioServiceCodeId              rsc RadioServiceCode     -     rsc RadioService as RadioService FROM sbi l radioservicecodes rsc INNER JOIN sbi l radioservicecodegroups rscg      ON rsc radioservicecodeid   rscg radioservicecodeid WHERE rscg radioservicegroupid IN       SELECT val FROM dbo fnParseArray  RadioServiceGroup           OR  RadioServiceGroup IS NULL   GROUP BY rsc RadioServiceCode rsc RadioServiceCodeId rsc RadioService ORDER BY rsc RadioServiceCode rsc RadioServiceCodeId rsc RadioService

User · Answer

Distinct and Group By generally do the same kind of thing  for different purposes     They both create a  working  table in memory based on the columns being Grouped on   or selected in the Select Distinct clause  - and then populate that working table as the query reads data   adding a new  row  only when the values indicate the need to do so      The only difference is that in the Group By there are additional  columns  in the working table for any calculated aggregate fields  like Sum    Count     Avg    etc   that need to updated for each original row read   Distinct doesn t have to do this    In the special case where you Group By only to get distinct values   And there are no aggregate columns in output   then it is probably exactly the same query plan     It would be interesting to review the query execution plan for the two options and see what it did      Certainly Distinct is the way to go for readability if that is what you are doing  When your purpose is to eliminate duplicate rows  and you are not calculating any aggregate columns

User · Answer

You could try a subquery   SELECT DISTINCT TEST   FROM       SELECT rsc RadioServiceCodeId          rsc RadioServiceCode     -     rsc RadioService as RadioService     FROM sbi l radioservicecodes rsc        INNER JOIN sbi l radioservicecodegroups rscg ON  rsc radioservicecodeid   rscg radioservicecodeid     WHERE rscg radioservicegroupid IN          select val from dbo fnParseArray  RadioServiceGroup               OR  RadioServiceGroup IS NULL       ORDER BY rsc RadioServiceCode rsc RadioServiceCodeId rsc RadioService   as TEST

User · Answer

Try this   ORDER BY 1  2   OR  ORDER BY rsc RadioServiceCodeId  rsc RadioServiceCode     -     rsc RadioService

User · Answer

While they are not the same thing  in one sense DISTINCT implies a GROUP BY  because every DISTINCT could be re-written using GROUP BY instead  With that in mind  it doesn t make sense to order by something that s not in the aggregate group     For example  if you have a table like this    col1  col2 ----  ----  1     1  1     2  2     1  2     2  2     3  3     1   and then try to query it like this   SELECT DISTINCT col1 FROM  table  WHERE col2  gt  2 ORDER BY col1  col2   That would make no sense  because there could end up being multiple col2 values per row  Which one should it use for the order  Of course  in this query you know the results wouldn t be that way  but the database server can t know that in advance   Now  your case is a little different  You included all the columns from the order by clause in the select clause  and therefore it would seem at first glance that they were all grouped  However  some of those columns were included in a calculated field   When you do that in combination with distinct  the distinct directive can only be applied to the final results of the calculation  it doesn t know anything about the source of the calculation any more   This means the server doesn t really know it can count on those columns any more   It knows that they were used  but it doesn t know if the calculation operation might cause an effect similar to my first simple example above   So now you need to do something else to tell the server that the columns are okay to use for ordering  There are several ways to do that  but this approach should work okay   SELECT rsc RadioServiceCodeId              rsc RadioServiceCode     -     rsc RadioService as RadioService FROM sbi l radioservicecodes rsc INNER JOIN sbi l radioservicecodegroups rscg      ON rsc radioservicecodeid   rscg radioservicecodeid WHERE rscg radioservicegroupid IN       SELECT val FROM dbo fnParseArray  RadioServiceGroup           OR  RadioServiceGroup IS NULL   GROUP BY rsc RadioServiceCode rsc RadioServiceCodeId rsc RadioService ORDER BY rsc RadioServiceCode rsc RadioServiceCodeId rsc RadioService

User · Answer

Try one of these    Use column alias   ORDER BY RadioServiceCodeId RadioService Use column position   ORDER BY 1 2   You can only order by columns that actually appear in the result of the DISTINCT query - the underlying data isn t available for ordering on

User · Answer

Try this   ORDER BY 1  2   OR  ORDER BY rsc RadioServiceCodeId  rsc RadioServiceCode     -     rsc RadioService

[sql] ORDER BY items must appear in the select list if SELECT DISTINCT is specified

Examples related to sql

Examples related to sql-server

Examples related to tsql