Change NULL position when sorting

Question

I am sorting a table. The violin 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 that 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 

Thus, the NULL value is treated as if it were higher than any other value. Can this be done?

Answer 1

The simplest thing is to add an additional sorting condition first:

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

Or you can try setting the maximum length of your data type:

 ORDER BY COALESCE(field,'99991231') DESC 

COALESCE / ISNULL works fine if you do not have "real" data using the same maximum value. If you do this and you need to distinguish them, use the first form.

Answer 2

Use the "end time" marker to replace zeros:

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

Answer 3

Be careful with queries that call functions for each row; they rarely scale.

This may be a problem for small datasets, but will be if they become large. This should be monitored by regularly conducting tests upon request. Database optimization is just the “Lock and Forget” operation if your data never changes (very rarely).

Sometimes it’s better to enter an artificial primary sort column, for example:

 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 use only result_set["everything except art_id"] in your programs.

This way you do not introduce (possibly) slow functions for each row; instead, you rely on a quick index search in the mydate column. And advanced execution engines can actually run these two queries at the same time, combining them when they are both finished.