Sql Server equivalent to Oracle's NULLS FIRST?

sql

best-practices

data-manipulation

sql-server

byNikita Barsukov·Nov 20, 2024

Put NULL values at the top of your sort result in SQL Server using CASE in your ORDER BY:

SELECT * FROM YourTable
ORDER BY CASE WHEN YourColumn IS NULL THEN 0 ELSE 1 END, YourColumn;

This command promotes NULL values by assigning a lower sort precedence (0) than non-nulls (1). So, voila! NULL values get VIP access in the ascending sort order.

Handling reverse gear - descending order

In the wild wild west of data manipulation, sometimes you need your data in a good old descending order. But, oh my SQL! You still want NULL values to show up first (on top). Just déja-vu the numeric priority and you're good to go.

SELECT * FROM YourTable
ORDER BY CASE WHEN YourColumn IS NULL THEN 0 ELSE 1 END, YourColumn DESC;

So you see, NULLs still gatecrash the party before your actual data, which then follows in descending order. Just like fashionably late celebs! 😎

Other sorts of funny business

The magical ISNULL function

If CASE isn't your thing, ISNULL() can be your knight-in-shining-armor and sort in your favor:

SELECT * FROM YourTable
ORDER BY ISNULL(YourColumn, 'AReallyLowValue'), YourColumn;

A word of advice: pick 'AReallyLowValue' that falls before your actual values (like picking a good movie seat - you know, prioritizing the view).

Specific non-date values

If you're sorting non-date columns, use this:

ORDER BY CASE WHEN YourColumn IS NULL THEN 0 ELSE 1 END, YourColumn DESC;

And for date columns, this should do the trick:

ORDER BY CASE WHEN YourDateColumn IS NULL THEN '19000101' ELSE YourDateColumn END;

A pro-tip here: we use '19000101' (sort of a "Ye Olde Date") to treat NULL dates as prehistoric artifacts!

Customizing the `CASE` statement

SQL Server's natural disposition

In default mode, NULL values are treated as the least valuable in an ascending sort and the most valuable in a descending order (SQL Server's mood swings!). Override this capricious behavior with a CASE:

-- 'LAST? Seriously?' - NULLS, probably!
ORDER BY CASE WHEN YourColumn IS NULL THEN 1 ELSE 0 END, YourColumn;

-- Top of the world, yet last in line - NULLS in descending order
ORDER BY CASE WHEN YourColumn IS NULL THEN 1 ELSE 0 END, YourColumn DESC;