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.
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 NULLINSERT rl_null SELECT NULLINSERT rl_null SELECT 1INSERT rl_null SELECT 2INSERT rl_null SELECT 3INSERT rl_null SELECT 4SELECT * FROM RL_NULL ORDER BY id ASCgives me the result.....id---NULLNULL1234I 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 nullIn your presentation layer, you can display themMadhivananFailing to plan is Planning to fail |
|
|
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) ASCbut 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 |
|
|
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 ASCThis way it works when ID is over 9999Tim S |
|
|
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 |
|
|
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 MadhivananFailing to plan is Planning to fail |
|
|
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 |
|
|
|
|
|