Conditional ORDER BY question - SQL Server Forums

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)

Conditional ORDER BY question

Author

Topic

BigRetina
Posting Yak Master

144 Posts

Posted - 2004-01-04 : 10:54:30

Salute,
I need to do a conditional order by clause...everything is wroking fine (in the code below) when I remove the (DESC) or put it out side the outer case. The problem is that I need to have different sort orders into the case options, How can I do that?


DECLARE @var1 int
DECLARE @var2 int

SELECT * from MyTable 
ORDER BY CASE @var1 WHEN 1 THEN CASE @var2 WHEN 1 THEN Col1 
					   ELSE Col2 DESC
			        END
	            ELSE Col2
	 END

Thanks In Advance

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-04 : 12:21:53

Think it is:

ORDER BY
case when @var1<>1 then col2 end,
case when @var1=1 and @var2=1 then col1 end,
case when @var1=1 and @var2<>1 then col2 end DESC

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-01-04 : 19:52:12

Stoad, with your DESC outside the case statements, won't that make them ALL be sorted DESC? I think you might have to do a separate series of CASE statements in order to assign ASC or DESC after you have done the series that decides which column.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-04 : 21:05:08

No, because the DESC refers only to the 3rd CASE.

BigRetina
Posting Yak Master

144 Posts

Posted - 2004-01-05 : 12:28:00

Thanks ALOT Stoad...It worked FINE!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources