THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Sort Values Ascending But NULLS Last

This is a frequent request in newsgroups and fora. People want to sort the column in ascending order but don't want the NULLS at the beginning.
Oracle has this syntax: ORDER BY ColumnName NULLS LAST;
SQL Server does not have this. But there are 2 ways to do this. The first one is by using case and the second one by using COALESCE and the maximum value for the data type in the order by clause.

The 2 approaches with a datetime data type

DECLARE @Temp table(Col datetime)
INSERT INTO @Temp VALUES(getdate())
INSERT INTO @Temp VALUES('2007-10-19 09:54:03.730')
INSERT INTO @Temp VALUES('2006-10-19 09:54:03.730')
INSERT INTO @Temp VALUES('2005-10-19 09:54:03.730')
INSERT INTO @Temp VALUES('2006-10-19 09:54:03.730')
INSERT INTO @Temp VALUES('2004-10-19 09:54:03.730')
INSERT INTO @Temp VALUES(NULL)
INSERT INTO @Temp VALUES(NULL)


SELECT *
FROM @Temp
ORDER BY COALESCE(Col,'9999-12-31 23:59:59.997')


SELECT *
FROM @Temp
ORDER BY CASE WHEN Col Is NULL Then 1 Else 0 End, Col



The 2 approaches with an integer data type

DECLARE @Temp table(Col int)
INSERT INTO @Temp VALUES(1)
INSERT INTO @Temp VALUES(555)
INSERT INTO @Temp VALUES(444)
INSERT INTO @Temp VALUES(333)
INSERT INTO @Temp VALUES(5656565)
INSERT INTO @Temp VALUES(3)
INSERT INTO @Temp VALUES(NULL)
INSERT INTO @Temp VALUES(NULL)


SELECT *
FROM @Temp
ORDER BY COALESCE(Col,'2147483647')


SELECT *
FROM @Temp
ORDER BY CASE WHEN Col Is NULL Then 1 Else 0 End, Col

Published Friday, October 19, 2007 10:23 AM by Denis Gobo
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

dbaduck said:

Great technique.  I will have to remember this one for a project I am working on.

October 24, 2007 3:24 PM
 

Drone said:

Wouldn't <code>isnull(Col,Val)</code> be more appropriate in this case?

Yesss it's nitpicky.

November 15, 2007 9:47 PM
 

Madhivanan said:

Other method

SELECT *

FROM @Temp

ORDER BY isnumeric(col) desc,col

December 5, 2007 4:59 AM
 

okayer said:

Fantastic and simple! Thank you.

September 29, 2008 5:08 AM
 

shivam said:

Was helpful.

Thanks.

March 29, 2011 1:50 PM
 

Phil said:

This is kinda poor really - what say you don't actually need the column you sort on in your results, and you have 1 billion results (not unreasonable for a large installation)?  That is an extra gig of data (minimum) you have to transfer just to throw it away.

Of course, you could use a temp table, but what an unnecessary kludge - the oracle syntax (actually ANSI syntax) handles it all right off the bat

May 15, 2011 8:33 PM
 

Maarten Jansonius said:

@Phil - ofcourse a builtin syntax is nicer, but that's not going to help anyone using sql server, where you don't have that syntax.

The point here is to find a good workaround!

Now, if you really want to minimize extra data then the "isnumeric([col])" or "case when [col] is null then 1 else 0 end" solution will add only 1 bit per row. That's not too much of a relative overhead size.

Looking at absolute overhead size is pointless I'd say - if your setup is good enough to handle millions of rows, then still, 1 bit per row extra ain't gonna kill ya.

May 19, 2011 7:48 AM
 

DB said:

I needed a solution that will work for ascending as well as descending order for strings, numbers, and date fields.

COALESCE and IsNumeric do not work with DATE fields. IsNull does not work with fields that have empty string instead of actual NULL.

In the end I came up with the modified version of "order by" that looks like this:

--for ASC sorting--

ORDER BY

CASE

WHEN sort_column Is NULL Then 1

WHEN RTrim(sort_column)='' Then 1

Else 0 End,

sort_column asc

--for DESC sorting--

ORDER BY

CASE

WHEN sort_column Is NULL Then 1

WHEN RTrim(sort_column)='' Then 1

Else 0 End,

sort_column desc

September 30, 2011 2:37 PM
 

pavan said:

Great Post...tnq

December 19, 2011 2:34 AM
 

sada said:

xzxz

February 3, 2012 12:37 PM
 

Grantly said:

Nice tips Gents

I like the ISNUMERIC trick too, handy...

Isn't there also an ISDATE function?  I think this can be used too...

July 26, 2012 4:02 PM
 

Michele said:

This was very helpful

August 27, 2012 4:47 PM
 

ivan said:

great post it helps me a lot. than ks guys

September 26, 2012 11:30 PM
 

harish said:

help me a lot

October 26, 2012 3:37 AM
 

Praveen said:

Thnx

December 28, 2012 1:07 AM
 

Andree said:

Clever techniques. Thanks!

December 31, 2012 4:26 AM
 

Joseph said:

Thanks Man, that is really helpful!!!

January 2, 2013 3:16 PM
 

Bruce said:

There's a much simpler way to do it:

-- ORDER BY x NULLS LAST

ORDER BY x IS NULL, x  

-- ORDER BY x NULLS FIRST

ORDER BY x IS NOT NULL, x

(x IS [NOT] NULL) is an expression that returns true/false (or 0/1 depending on your sql dialect) depending on the nullness of x and can be used anywhere any other expression is used.

July 30, 2013 7:49 PM
 

SOlanki said:

How ro sort more than one CHAR column with specified condition in CASE

February 12, 2014 5:07 AM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement