Returning NULL's at the end of a recordset

Question

Returning NULL's at the end of a recordset

dynamic

Grasshopper

Points: 11
More actions
November 6, 2001 at 9:22 am

#77807

I have just stumbled upon a (TSQL language supported) solution that I have been in search of for a LONG time and wanted to share.
How many times have you wanted to use ORDER BY ASC on a field that can contain NULL's and did not want the NULL's returned at the top of the recordset?
I decided to try and use a CASE w/in the ORDER BY clause and to my amazement it was supported.
Now you can write code like this :
SELECT * from systypes
ORDER BY CASE WHEN prec IS NULL THEN 2 ELSE 1 END, prec
Anyways, if you guys knew this already, then more power to you...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Paul Ibison SSCertifiable Points: 5303 More actions · Answer 1

I for one haven't seen this before. Previously I'd created a dummy column and used a case statement to populate it in the select, then used ordered by this dummy column. My method worked, but yours is simpler and returns less data.

Paul Ibison

email: Paul.Ibison@btinternet.com

Paul Ibison
Paul.Ibison@replicationanswers.com

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

What a great T-SQL trick.

Thanks for sharing!!

Steve Jones

steve@dkranch.net

nigelrivett SSCertifiable Points: 5472 More actions · Answer 3

You can also swap the order (the literal just has to be the same case as the field).

order by case when @dir = 'up' then 1 else prec end desc, prec asc

You can use the same technique for ommitting fields from the sort.

Unfortunately you can't include the direction inside a case statement (unless it's changed lately).

a common use is to return recs from a event log.

order by case when status = 'failed' then 1 when status = 'warning' then 2 when 'success' then 3 end

Cursors never.
DTS - only when needed and never to control.