Changing NULL's position in sorting

Question

I am sorting a table. The fiddle can be found here.

CREATE TABLE test
(
field date NULL
);

INSERT INTO test VALUES
('2000-01-05'),
('2004-01-05'),
(NULL),
('2008-01-05');

SELECT * FROM test ORDER BY field DESC;

The results I get:

2008-01-05
2004-01-05
2000-01-05
(null)

However I need the results to be like this:

(null)
2008-01-05
2004-01-05
2000-01-05

So the NULL value is treated as if it is higher than any other value. Is it possible to do so?

Answer 1

Easiest is to add an extra sort condition first:

ORDER BY CASE WHEN field is null then 0 else 1 END,field DESC

Or, you can try setting it to the maximum of its datatype:

ORDER BY COALESCE(field,'99991231') DESC

COALESCE/ISNULL work fine, provided you don't have "real" data using that same maximum value. If you do, and you need to distinguish them, use the first form.