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.

OUTPUT

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