Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Ordering by exception

Author  Topic 

sqlbug
Posting Yak Master

201 Posts

Posted - 2013-01-29 : 19:00:05
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.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-01-29 : 20:27:16
I tend to cheat:
select
[distinct if you want it] column list I want
from
(
select column list + a calculated order by column as whatever
) as X
order by x.whatever

Because I am usually too lazy to work out exactly the bits I need.
Go to Top of Page

sqlbug
Posting Yak Master

201 Posts

Posted - 2013-01-30 : 11:11:21
I like that attitude, thanks.
Go to Top of Page
   

- Advertisement -