How do I Fix SQL Server error: Order by items must appear in the select list if Select distinct is s
- by Paula DiTallo 2007-2009 All Rights Reserved
There's more than one reason why you may receive this error, but the most common reason is that your order by statement column list doesn't correlate with the values specified in your column list when you happen to be using DISTINCT. This is usually easy to spot and resolve.
A more obscure reason may be that you are using a function around one of the selected columns --but omitting to use the same function around the same selected column name in the order by statement.
Here's an example:
select distinct upper(columnA)
from [evaluate].[testTable]
order by columnA asc
This statement will cause the "Order by items must appear in the select list if SELECT DISTINCT is specified." error to appear not because distinct was used, but because the order by statement did not utilize the upper() fundtion around colunnA. To correct this error, do this:
select distinct upper(columnA)
from [evaluate].[testTable]
order by upper(columnA) asc
