How do you display NULL values at the end of a resultset?

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.