November 14, 2013 at 7:05 am
Select Distinct Category from menuGroup
ORDER BY (CASE Category
WHEN 'SVP' THEN 1
WHEN 'OS' THEN 2
WHEN 'Classification' THEN 4
When 'Status' THEN 3
Else 5 END) ASC
Error:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
Can anyone help me understand the error message? Thank you.
November 14, 2013 at 7:35 am
halifaxdal (11/14/2013)
Select Distinct Category from menuGroup
ORDER BY (CASE Category
WHEN 'SVP' THEN 1
WHEN 'OS' THEN 2
WHEN 'Classification' THEN 4
When 'Status' THEN 3
Else 5 END) ASC
Error:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.Can anyone help me understand the error message? Thank you.
The case expression in the Order By is a derived column and that column does not exist in the main query.
One option would be to move the derived column to the query, then you can use it to order.
Select Distinct Category ,
CASE Category
WHEN 'SVP' THEN 1
WHEN 'OS' THEN 2
WHEN 'Classification' THEN 4
When 'Status' THEN 3
Else 5
END as SortOrder
from menuGroup
ORDER BY SortOrder ASC
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 14, 2013 at 7:54 am
Thanks for the clarification
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply