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

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-05-07 : 00:18:22

hey all..
how do i order column date with null at the bottom?
eg:
date
1 jan 99
2 jan 99
NULL

Not like normal :-
NULL
1 jan 99
2 jan 99

thanks in advance

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-07 : 00:33:44

put a dummy column and order by that column along with the required one

select *, col2 = (case when col1 is null then 1 else 0 end) from <table name> order by col2, <column name>

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-05-07 : 00:34:28

order by coalesce([date], '2100-01-01')

e4 d5 xd5 Nf6

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-05-07 : 01:40:56

tq pbguy, blindman...
ill go for blindman's .. thanks blindman.. i like it :D

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

BManTYA
Starting Member

6 Posts

Posted - 2007-05-19 : 03:02:54

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-19 : 05:52:08

BManTYA, you should start a new thread for your question.


case when Game is NOT NULL then 1
     else 2
     end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources