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.