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?

possible duplicate of [ORDER BY DATE showing NULLS first then most recent dates](http://stackoverflow.com/questions/821798/order-by-date-showing-nulls-first-then-most-recent-dates) — GarethD, Jun 08 '12 at 10:01

score 4 · Answer 1 · answered Jun 08 '12 at 09:55

4

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

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

answered Jun 08 '12 at 09:55

Mitch Wheat

295,962
43
465
541

Um, you want the end of time, not the start of time, when ordering `desc` and wanting `null`s first – Damien_The_Unbeliever Jun 08 '12 at 09:56
Aha! Here we go! Yet another day when I learn something valuable. Thank you :) – Andrius Naruševičius Jun 08 '12 at 09:58

score 4 · Accepted Answer · answered Jun 08 '12 at 09:56

4

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.

answered Jun 08 '12 at 09:56

Damien_The_Unbeliever

234,701
27
340
448

Yes, the data is of a normal range and that is current days, years, (not more than +-10 years) :) Thanks for the answer :) – Andrius Naruševičius Jun 08 '12 at 10:01
The first option is my natural inclination. I don't have access to a DB at present to test my assumption, but I would expect this; If the result-set is already naturally ordered, the latter will be planned with a more expensive re-ordering routine, due to the sort field being obfuscated by `ISNULL()`, and so the optimiser not being able to `know` about the relation between the datas natural order and desired final order. *(Did that make any sense?)* – MatBailie Jun 08 '12 at 10:21

score 1 · Answer 3 · answered Jun 08 '12 at 10:05

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.

Changing NULL's position in sorting

3 Answers3

Linked