更改NULL在排序中的位置 [英] 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;

我得到的结果:

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

因此将NULL值视为高于任何其他值.有可能这样做吗?

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可以正常工作，但前提是您没有使用相同最大值的真实"数据.如果这样做，并且需要区分它们，请使用第一种形式.

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.

这篇关于更改NULL在排序中的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！