Actually, this question can be extended to: How can you create a custom ORDER BY. But let’s first have a look at the following query:
USE PUBS
GO
SELECT
title_id
, type
, price
, royalty
FROM
titles
ORDER BY
royalty
title_id type price royalty
——– ———— ——————— ———–
MC3026 UNDECIDED NULL NULL
PC9999 popular_comp NULL NULL
PS1372 psychology 21.5900 10
BU1032 business 19.9900 10
BU1111 business 11.9500 10
BU7832 business 19.9900 10
PS2106 psychology 7.0000 10
PS3333 psychology 19.9900 10
PS7777 psychology 7.9900 10
TC3218 trad_cook 20.9500 10
PC8888 popular_comp 20.0000 10
TC7777 trad_cook 14.9900 10
MC2222 mod_cook 19.9900 12
PS2091 psychology 10.9500 12
TC4203 trad_cook 11.9500 14
PC1035 popular_comp 22.9500 16
MC3021 mod_cook 2.9900 24
BU2075 business 2.9900 24
(18 row(s) affected)
As you can see, the column royalty contains NULL values. Now, someone might be tempted to request that these rows be at the end to the resultset. Probably the easiest way to do this would be to rewrite the query like:
SELECT
title_id
, type
, price
, royalty
FROM
titles
ORDER BY
royalty DESC
Now, those rows are shown at the bottom of the resultset. However, now your aesthetical feeling might be a bit offended, because royalty is shown with the highest value first, and then descending. What now? An easy way is to rewrite the query like:
SELECT
title_id
, type
, price
, royalty
FROM
titles
ORDER BY
CASE WHEN royalty IS NULL THEN 1000 ELSE royalty END
Through the use of the CASE expression, you can filter out the NULL values and assign them the artificial 1000, which is far more than the highest existing royalty to make sure they will move to the end of the resultset. So far, so good. This works, but it isn’t a practicable solution, as in most cases you don’t know in advance the maximum value of the column you want to sort. To workaround this limitation, you can try:
DECLARE @max_value INT
SELECT @max_value = MAX(royalty)+1 FROM titles
SELECT
title_id
, type
, price
, royalty
FROM
titles
ORDER BY
CASE WHEN royalty IS NULL THEN @max_value ELSE royalty END
This works fine for all cases where you need to sort on a numeric column, but will fail for character columns. And you need an extra query to get the maximum value and increment it by 1. So, this is still not a good generic solution. The best thing you can do here is to create a completely artificial expression in your ORDER BY like this:
SELECT
title_id
, type
, price
, royalty
FROM
titles
ORDER BY
CASE WHEN royalty IS NULL THEN 1 ELSE 0 END
, royalty
We still use a CASE expression, but don’t include any column from the underlying table in it. We use a “two-column” ORDER BY. We first assign a 1 for all columns which contain NULL. And 0 to those columns which do not contain NULL. And we order by royalty. This way we get our desired resultset, and our solution doesn’t care about the underlying data type of the column we use in the ORDER BY, and the impact on performance is negligible, if there is any at all.