Hi,
I need to make some exception to the order by rule, so I am trying to use a CASE statement there and having error.
The problem: I have a case on the column that I need to order by, like:
SELECT DISTINCT ......, CASE STN_TYPE WHEN '2' THEN '(Portable)' + STN_NAME ELSE STN_NAME END AS "STATION NAME"
...
...
ORDER BY ...,
CASE WHEN CHARINDEX('(Portable)', STN_NAME) = 0 THEN "STATION NAME" ELSE "STATION NAME" END DESC
Doesn't matter what name (AS STN_NAME or AS "STATION NAME") I try to use in the SELECT, I get "ORDER BY items must appear in the select list if SELECT DISTINCT is specified." error -
The reason for which is it's not finding the "STATION NAME" in the ORDER BY CLAUSE (If I did not use CASE in the ORDER BY and simply used "STATION NAME", it would run fine).
Any idea how I can fix it - and keep the CASE for the ORDER BY clause?
Thanks.
Ordering by exception
Posted - 01/29/2013 : 19:00:05
