CODEWITHSUNDEEP
sqlsql-serversql-server-2008sortingnull
Andrius Naruševičius
Andrius Naruševičius

Reputation: 8578

Changing NULL's position in sorting

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?

Upvotes: 2

Views: 215

Answers (3)

paxdiablo
paxdiablo

Reputation: 882146

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.

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

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.

Upvotes: 4

Mitch Wheat
Mitch Wheat

Reputation: 300719

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

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

Upvotes: 4

Related Questions