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

sql-server-null-sort

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.

OUTPUT

sql-server-null-ascending


Did you like this post?
kick it on DotNetKicks.com
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 www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan

 
  Feedback:

comments

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

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