SQL SELECT Ordering columns with Null Values

Question

My question is similar to this one: How to display a table order by code (like 01, 02… then null columns)?, but for SQL Server.

In short, I have a SELECT statement, that returns the following:

ColumnA ColumnB
X       NULL
Y       1
Z       2

..where the ordering is done by ColumnB.

How can we force the (columnB = NULL) type of rows to the bottom? ie, the expected result is this:

ColumnA ColumnB
Y       1
Z       2
X       NULL

Thank you SOF community.

Answer 1

You can also use isnull:

select * from thetable order by isnull(columnb, 99999)

isnull will replace null with the value you provide to it, so in this case, if the column is null, it will replace it with 99999. You can set the value to some big number so it will be at the bottom of the order.

Answer 2

...or in order to avoid value clashing...

SELECT 
   ColumnA, 
   ColumnB
FROM YourTable
ORDER BY 
   CASE WHEN ColumnB IS NULL THEN 1 ELSE 0 END ASC,
   ColumnB