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?
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.
Use a 'end of time' marker to replace nulls:
SELECT * FROM test
ORDER BY ISNULL(field, '9999-01-01') DESC;
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.
