ORDER BY DATE 先显示 NULLS，然后是最近的日期 [英] ORDER BY DATE showing NULLS first then most recent dates

问题描述

我有一个执行 select 语句的存储过程.我希望我的结果按日期字段排序，并首先显示所有日期为 NULL 的记录，然后显示最近的日期.

I have a stored procedure which executes a select statement. I would like my results ordered by a date field and display all records with NULL dates first and then the most recent dates.

语句如下:

SELECT a,b,c,[Submission Date]
FROM someView
ORDER BY [Submission Date] ASC

现在这将首先显示提交日期为 NULL 的所有记录，但是当我到达包含日期值的行时，它们不是视图中的最新日期.

Now this will display all records with NULL Submission Dates first, but when I get to rows that have date values in them, they are not the most recent dates in the view.

如果我用 DESC 替换 ASC，那么我会按照我想要的顺序获取日期，但 NULL 值位于结果集的底部.

If I replace ASC with DESC, then I get the dates in the the order I want, but the NULL values are at the bottom of my result set.

有什么方法可以构造我的查询，以便我可以在顶部显示空值，然后在有日期值时将它们从最近到最旧的降序排列?

Is there any way to structure my query so that I can display the null values at the top and then when there are date values, to order them descending most recent to oldest?

推荐答案

@Chris，你已经差不多了.

@Chris, you almost have it.

ORDER BY (CASE WHEN [Submission Date] IS NULL THEN 1 ELSE 0 END) DESC, 
         [Submission Date] DESC

我个人更喜欢这个，而不是创造神奇的数字".幻数几乎总是一个等待发生的问题.

I personally prefer this a lot better than creating "magic numbers". Magic numbers are almost always a problem waiting to happen.

这篇关于ORDER BY DATE 先显示 NULLS，然后是最近的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！