Sorting with NULLs - 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

Sorting with NULLs

Author

Topic

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-04-20 : 12:01:13

I don't have a specific problem, but more of a general question: let's say I've got a field that accepts integers and NULLs. Can you sort by that field in ascending order but with the NULLs at the end?

I was thinking you could change the NULLs to a number like 9999 so it could show up at the end, but what if you wanted to keep the NULL? Or what if you wanted to put a message like "TBD" or "N/A"? Would a SELECT-UNION-SELECT be the best choice?

Like I said before, I'm not actually working on anything right now that has this problem, but I was just curious about what to do if something like that did end up being an issue.

Thanks for your help!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-20 : 12:03:52

You could usea sort condition like


ORDER BY
  case when col is null then 1 else 0 end, col

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-20 : 12:05:19

order by isnull(number,9999) asc
If you have NA or TBD in that field, then it isn't a number
Jim

Everyday I learn something that somebody else already knew

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-04-20 : 12:13:50

quote:
Originally posted by jimf

order by isnull(number,9999) asc
If you have NA or TBD in that field, then it isn't a number
Jim

Right - if it isn't a number, then values like:
3, 50, 101, 200
would sort like:
101, 200, 3, 50
if I'm not mistaken? Is there a way to make it display NA or TBD without changing the value in the database while still sorting all of the numbers in ascending order above it?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-21 : 08:00:29

This would be done much better (and probably safer) in the front end, as SQL doesn't like it when you mix data types. I prefer the 9999 idea. However,
select isnull(convert(varchar(10),col),'NA') as newcol
From yourTable
order by newcol

Jim

Everyday I learn something that somebody else already knew

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-21 : 08:14:34

select s = coalesce(CONVERT(varchar(20),i), 'NA')
from tbl
order by case when i is null then 2 else 1 end,i

==========================================
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.

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-04-21 : 12:11:39

Interesting - so you can put a CASE statement in the ORDER BY? I knew I'd learn something new.

quote:
Originally posted by sunitabeck

You could usea sort condition like

ORDER BY
case when col is null then 1 else 0 end, col

So what exactly does it mean to ORDER BY 1? ORDER BY 0? Or are those supposed to be replaced by field names?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-21 : 12:16:58

quote:
Originally posted by Aleph_0

Interesting - so you can put a CASE statement in the ORDER BY? I knew I'd learn something new.

quote:
Originally posted by sunitabeck

You could usea sort condition like

ORDER BY
case when col is null then 1 else 0 end, col

So what exactly does it mean to ORDER BY 1? ORDER BY 0? Or are those supposed to be replaced by field names?

No, you don't replace the 0 and 1 by field names. Just replace "col" by the column name you want to sort by.

It is a two-level sort, just like if you were to say: "order by Lastname, Firstname". In that case, it would first order by the Lastname and then by the First name.

Exactly like that, we are asking order condition to be
first sort on: case when col is null then 1 else 0 end
next sort on: col

The case statement puts a 1 when col is null and 0 when it is not. And since 0 comes before 1, everything with a 0 i.e., everything that is non-null will be sorted first, followed by the nulls. The second sort - the sort on col will order the non-nulls just as it did before.

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-04-21 : 16:58:07

quote:
Originally posted by sunitabeck
The case statement puts a 1 when col is null and 0 when it is not. And since 0 comes before 1, everything with a 0 i.e., everything that is non-null will be sorted first, followed by the nulls. The second sort - the sort on col will order the non-nulls just as it did before.

I get it! So the 0 and 1 aren't even displayed; it's like a fake field that's only used to sort, right? (Pardon my unsophisticated jargon.) Is this really as clever as it seems to me? You guys probably pull these tricks off all the time in your sleep, but hey, I find it amazing and I'm gonna enjoy it!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-21 : 17:07:52

Remember when I recommended SQL for Smarties and The Guru's Guide to Transact-SQL: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159315

Now you know why.

(and where the rest of us got these tricks)
(besides SQLTeam of course!)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources