SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Ordering by exception
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlbug
Posting Yak Master

Canada
201 Posts

Posted - 01/29/2013 :  19:00:05  Show Profile  Reply with Quote
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 - 01/29/2013 :  20:27:16  Show Profile  Reply with Quote
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

Canada
201 Posts

Posted - 01/30/2013 :  11:11:21  Show Profile  Reply with Quote
I like that attitude, thanks.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 1.09 seconds. Powered By: Snitz Forums 2000