Stored Proc using Case with NULL

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)

Stored Proc using Case with NULL

Author

Topic

BManTYA
Starting Member

6 Posts

Posted - 2007-05-20 : 03:03:20

I am fairly new to Stored Procedures and was wondering if there was a way to sort ASC but with NULLS at the end of the list. Here is the code I have so far.

ALTER PROCEDURE [dbo].[XBF_SelectListAll]
AS
/* SET NOCOUNT ON */

SELECT Gamertag, Game, Score, Profile, Avatar, Presence, Status, Zone, Reputation, LastSeen, Updated, UpdatedBy
FROM XBF_GamerData
ORDER BY
CASE Status
WHEN 'Online' THEN 1
WHEN 'Away' THEN 2
WHEN 'Offline' THEN 3
WHEN 'Unknown' THEN 4
WHEN 'Removed' THEN 5
ELSE NULL
END,
Game , Gamertag

RETURN

The only problem is that the NULLS for game are at the top of the sort and I want them at the bottom without having all the games in DESC order. Was thinking of...

CASE Game
WHEN 'Game IS NOT NULL' THEN 1
WHEN 'Game IS NULL' THEN 2
ELSE NULL
END,

When I try it it just ignores the game all together.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-20 : 03:29:51

[code]
ORDER BY
case when Game is NOT NULL then 1
else 2
end
[/code]

BManTYA
Starting Member

6 Posts

Posted - 2007-05-20 : 11:47:40

I tried that and it still just ignores the game sort.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-20 : 12:10:07

order by coalesce(game,'zzzz')

or

order by case when game is null then 2 else 1 end, game

- Jeff
http://weblogs.sqlteam.com/JeffS

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-05-20 : 12:43:16

Thhis should do what you want.


order by
	case Status 
	when 'Online'	then 1
	when 'Away'	then 2
	when 'Offline'	then 3
	when 'Unknown'	then 4
	when 'Removed'	then 5
	else 99
	end,
	case
	when Game is not null then 1
	else 99
	end,
	Game,
	Gamertag

CODO ERGO SUM

BManTYA
Starting Member

6 Posts

Posted - 2007-05-21 : 08:56:40

Thanks Mike for the help. It worked perfect.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources