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

...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

Answer 2

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 3

hoping to help someone, I just wanted to add that I have had a similiar issue, using row_number and partition by - when it is zero put it at the end sort of thing and I used the script below (partial view):

   ,T.MONTHS_TO_AUTOGROWTH
   ,the_closest_event=ROW_NUMBER() OVER (PARTITION BY SERVERID, DRIVE ORDER BY 
                                      CASE WHEN MONTHS_TO_AUTOGROWTH > 0 THEN MONTHS_TO_AUTOGROWTH ELSE 9999 
                                      END ) 

the result is ordered by MONTHS_TO_AUTOGROWTH but zero comes last