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屋!