order by case - 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

General SQL Server Forums

New to SQL Server Programming

order by case

Author

Topic

craigmacca
Posting Yak Master

142 Posts

Posted - 2011-07-08 : 10:38:09

Hi i need a order by case statement but the data values are different types not sure how i can do this??

SELECT t.Id, t.ParentId, t.Title, t.Ordinal, t.HelpTopicId
FROM Tree t
ORDER BY CASE
WHEN @OrderBy = 'Title' THEN t.Title -- string
WHEN @OrderBy = 'Latest' THEN t.DateCreated -- datetime
WHEN @OrderBy = 'Ordinal' THEN t.Ordinal -- int
ELSE t.Ordinal -- int
END

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-08 : 10:42:49

You could convert all to varchar but maybe easier to

ORDER BY
CASE WHEN @OrderBy = 'Title' THEN t.Title end , -- string
CASE WHEN @OrderBy = 'Latest' THEN t.DateCreated end , -- datetime
CASE WHEN @OrderBy = 'Ordinal' THEN t.Ordinal end , -- int
t.Ordinal -- int

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-08 : 10:42:51

convert all to string.

for datetime, should convert using style 121 or 112

for integer, it should be converted to a fixed length string with the numbers right aligned


right(space(10) + convert(varchar(10), Ordinal), 10)

KH
[spoiler]Time is always against us[/spoiler]

craigmacca
Posting Yak Master

142 Posts

Posted - 2011-07-08 : 11:18:06

if i do that will the datetime still order correctly?? as it will be a nvarchar?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-08 : 11:32:57

Nope it will be a datetime.
If you want to convert to a varchar then use style 121 or 126 which will be in order of decreasing components.
I would go for the separate entries though as it makes things a lot easier than a convert.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

craigmacca
Posting Yak Master

142 Posts

Posted - 2011-07-11 : 04:25:13

thanks for your help , i have tried that but i get a syntax error, any ideas?

ORDER BY CASE
WHEN @OrderBy = 'title' THEN t.Title
WHEN @OrderBy = 'lastest' THEN convert(varchar(20), t.DateCreated), 121)
ELSE right(space(10) + convert(varchar(10), t.Ordinal), 10)
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-11 : 04:38:43

No need to convert.

SELECT		t.Id,
		t.ParentId,
		t.Title,
		t.Ordinal,
		t.HelpTopicId
FROM		dbo.Tree AS t
ORDER BY	CASE @OrderBy
			WHEN 'Title' THEN t.Title
			ELSE NULL
		END,
		CASE @OrderBy
			WHEN 'Latest' THEN t.DateCreated
			ELSE NULL
		END,
		CASE @OrderBy
			WHEN 'Ordinal' THEN t.Ordinal
			ELSE NULL
		END

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources