问题描述
我正在尝试按日期订购.我希望最近的日期首先出现.这很容易，但是有许多记录为空，并且出现在任何具有日期的记录之前.
I am trying to order by date. I want the most recent dates coming in first. That's easy enough, but there are many records that are null and those come before any records that have a date.
我尝试了一些没有成功的方法:
I have tried a few things with no success:
ORDER BY ISNULL(Next_Contact_Date, 0)
ORDER BY ISNULL(Next_Contact_Date, 999999999)
ORDER BY coalesce(Next_Contact_Date, 99/99/9999)
如何按日期排序并使空值排在最后?数据类型为smalldatetime.
How can I order by date and have the nulls come in last? The data type is smalldatetime.
推荐答案
smalldatetime 的范围截止到 2079 年 6 月 6 日，因此您可以使用
smalldatetime has range up to June 6, 2079 so you can use
ORDER BY ISNULL(Next_Contact_Date, '2079-06-05T23:59:00')
如果没有合法记录会有那个日期.
If no legitimate records will have that date.
如果这不是您想依赖的更强大选项的假设，那就是对两列进行排序.
If this is not an assumption you fancy relying on a more robust option is sorting on two columns.
ORDER BY CASE WHEN Next_Contact_Date IS NULL THEN 1 ELSE 0 END, Next_Contact_Date
以上两个建议都不能使用索引来避免排序，但给出了相似的外观计划.
Both of the above suggestions are not able to use an index to avoid a sort however and give similar looking plans.
如果存在这样的索引的另一种可能性是
One other possibility if such an index exists is
SELECT 1 AS Grp, Next_Contact_Date
FROM T
WHERE Next_Contact_Date IS NOT NULL
UNION ALL
SELECT 2 AS Grp, Next_Contact_Date
FROM T
WHERE Next_Contact_Date IS NULL
ORDER BY Grp, Next_Contact_Date
这篇关于SQL Server ORDER BY 日期和空值最后的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！