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

Use a 'end of time' marker to replace nulls:

SELECT * FROM test 
ORDER BY ISNULL(field, '9999-01-01') DESC; 

Answer 2

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.

Answer 3

Be wary of queries that invoke per-row functions, they rarely scale well.

That may not be a problem for smaller data sets but will be if they become large. That should be monitored by regularly performing tests on the queries. Database optimisation is only a set-and-forget operation if your data never changes (very rare).

Sometimes it's better to introduce an artificial primary sort column, such as with:

select 1 as art_id, mydate, col1, col2 from mytable where mydate is null
union all
select 2 as art_id, mydate, col1, col2 from mytable where mydate is not null
order by art_id, mydate desc

Then only use result_set["everything except art_id"] in your programs.

By doing that, you don't introduce (possibly) slow per-row functions, instead you rely on fast index lookup on the mydate column. And advanced execution engines can actually run these two queries concurrently, combining them once they're both finished.