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.