August 26, 2011

SQL Server: Handle Nulls While Sorting Data in Ascending Order

The SQL Server ORDER BY Clause is used to order the result sets by a set of columns. If you use ORDER BY col ASC, NULL values come first, if you do ORDER BY col DESC, NULL values are shown last, but remaining values are ordered in descending order.

However what if you want order the result sets by a particular column in ascending order and also keep all NULL values in last rows? It is possible with the following method:

Consider the following table


Execute the following statement

select number from @t
order by case when number is null then 1 else 0 end, number

The above code sets the serial number based on the ascending order of the values. When the column number is NULL, it sets to 1 otherwise it sets to 0. The second column in the   ORDER BY clause (number) is used to order the numbers in ascending order,  keeping NULL values last.



Did you like this post?
kick it on
subscribe via rss subscribe via e-mail
print this post follow me on twitter

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at and a moderator at His T-sql blog is at



0 Responses to "SQL Server: Handle Nulls While Sorting Data in Ascending Order"

Copyright © 2009-2014 All Rights Reserved for by Suprotim Agarwal | Terms and Conditions