How to use "ORDER BY CASE WHEN" for mutiple sort i

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 2005 Forums

Transact-SQL (2005)

How to use "ORDER BY CASE WHEN" for mutiple sort i

Author

Topic

n8900498
Starting Member

1 Post

Posted - 2007-09-05 : 15:24:14

SELECT A,B,C,D FROM Alhpabet ORDER BY CASE

WHEN @SortExpression = 'A' THEN A,B,C,D

WHEN @SortExpression = 'B' THEN B,D,C

WHEN @SortExpression = 'C' THEN C,D,A

END

this will work if you only have 1 sort item in the list, but how do I have more than one sort item e.g. 'THEN C,D,A'

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-05 : 15:27:51

case when @sortexperssion='a' then 1
when @sortexperssion='b' then 2
etc....

a will always be sorted as first,then b then c then d...

quote:
Originally posted by n8900498

SELECT A,B,C,D FROM Alhpabet ORDER BY CASE

WHEN @SortExpression = 'A' THEN A,B,C,D

WHEN @SortExpression = 'B' THEN B,D,C

WHEN @SortExpression = 'C' THEN C,D,A

END

this will work if you only have 1 sort item in the list, but how do I have more than one sort item e.g. 'THEN C,D,A'

--------------------
keeping it simple...

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-09-05 : 20:38:10

ORDER BY
CASE WHEN @SortExpression = 'A' THEN A WHEN @SortExpression = 'B' THEN B ELSE THEN C END,
CASE WHEN @SortExpression = 'A' THEN B WHEN @SortExpression = 'B' THEN D ELSE THEN D END,
CASE WHEN @SortExpression = 'A' THEN C WHEN @SortExpression = 'B' THEN C ELSE THEN A END,
CASE WHEN @SortExpression = 'A' THEN D ELSE 'X' END -- or ELSE 0 if it is integer field

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources