Sorting on multiple columns with CASE

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

SQL Server 2000 Forums

Transact-SQL (2000)

Sorting on multiple columns with CASE

Author

Topic

dcarva
Posting Yak Master

140 Posts

Posted - 2004-05-15 : 11:06:02

Hello,

The simplified query (in a stored procedure) below works great.

select *
from maps
order by
CASE WHEN @sortorder = 1 THEN Api END DESC,
CASE WHEN @sortorder = 0 THEN Api END DESC

But when I try to sort on multiple columns, I get a t-sql compiler error and the proc doesn't compile.

select *
from maps
order by
CASE WHEN @sortorder = 1 THEN Api, wellno END DESC,
CASE WHEN @sortorder = 0 THEN Api, wellno END DESC

How can I write this so that I can sort on multiple columns?

Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-05-15 : 11:48:19

even if your code compiled, your sort is exactly the same in either case ... both situations (@sortorder of 1 or 0) result in "API,WellNo DESC".

what are you trying to accomplish?

- Jeff

dcarva
Posting Yak Master

140 Posts

Posted - 2004-05-15 : 12:29:50

Sorry, I typed it wrong. I meant:

select * from maps order by
CASE WHEN @sortorder = 1 THEN Api, wellno END DESC,
CASE WHEN @sortorder = 0 THEN Api, wellno END ASC

Based on the sort order passed into the sp, I want to sort on 2 columns.

Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-05-15 : 12:36:19

select * from maps
order by api,
CASE WHEN @sortorder = 1 THEN wellno ELSE Null END DESC,
CASE WHEN @sortorder = 0 THEN wellno ELSE Null END ASC

- Jeff

dcarva
Posting Yak Master

140 Posts

Posted - 2004-05-15 : 23:00:52

Cool. I'll give it a try.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources