SQL SELECT Ordering columns with Null Values
15,217
Solution 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
Solution 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.
Related videos on Youtube
Comments
-
FMFF almost 2 years
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.