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
 Old Forums
 CLOSED - General SQL Server
 order by NULL

Author  Topic 

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2005-07-20 : 10:31:23
CREATE TABLE rl_null (id int )

INSERT rl_null SELECT NULL
INSERT rl_null SELECT NULL
INSERT rl_null SELECT 1
INSERT rl_null SELECT 2
INSERT rl_null SELECT 3
INSERT rl_null SELECT 4

SELECT * FROM RL_NULL ORDER BY id ASC

gives me the result.....

id
---
NULL
NULL
1
2
3
4

I want the NULLs to apprear at the last.

How to achieve this?

------------------------
I think, therefore I am - Rene Descartes

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-20 : 10:45:22
Any reason you want the result like this?
I think you can have two queries one with not null order by id and other with null
In your presentation layer, you can display them

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2005-07-20 : 10:49:22
Following query also solves the purpose....

SELECT * FROM RL_NULL ORDER BY (case WHEN ID IS NULL THEN '9999' ELSE ID END) ASC

but i want to check whether there is a easier way...oracle has some thing like NULLS_FIRST and NULLS_LAST clause for ORDER BY ...

------------------------
I think, therefore I am - Rene Descartes
Go to Top of Page

TimS
Posting Yak Master

198 Posts

Posted - 2005-07-20 : 11:02:28
SELECT *
FROM RL_NULL
ORDER BY (case WHEN ID IS NULL THEN 1 ELSE 0 END), id ASC

This way it works when ID is over 9999

Tim S
Go to Top of Page

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2005-11-11 : 08:29:08
One more neat solution,
SELECT * FROM RL_NULL ORDER BY ISNULL(id,9999) ASC



------------------------
I think, therefore I am - Rene Descartes
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-11 : 08:36:58
Nice. It is better to have id value as big as possible. otherwise if id has the value more than 9999 your query will sort it in other order

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-11-11 : 08:58:19
I use TimS's solution, so there is never any chance of the actual "id" value colliding with the coalesced value.

Kristen
Go to Top of Page
   

- Advertisement -