ORDER BY DATE showing NULLS first then most recent

问题:

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.

The statement looks like this:

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

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.

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?

回答1:

@Chris, you almost have it.

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

[Edit: #Eppz asked me to tweak the code above as currently shown]

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

回答2:

You can do something like this put the NULL's at the bottom:

ORDER BY [Submission Date] IS NULL DESC, [Submission Date] ASC

回答3:

Standard SQL (ISO/IEC 9075-2:2003 or later - 2008) provides for:

ORDER BY SomeColumn NULLS FIRST

Most DBMS do not actually support this yet, AFAIK.

回答4:

try

SELECT a,b,c,[Submission Date]
FROM someView
ORDER BY isnull([Submission Date],cast('2079/01/01' as datetime)) ASC

回答5:

OrderBy="ColumnName = NULL desc, ColumnName desc"

回答6:

I have another suggestion that might be simpler than all the others:

For SQL Server, most of the options don't work, except the case ones.

I found that this actually works great to me: ORDER BY ISNULL(Submission_Date, GETDATE()) DESC

In the order bit of the query, I assign the GETDATE() value to the Submittion_Date values that are null, and the order comes out correctly.

回答7:

try this

SELECT a,b,c,[Submission Date] FROM someView ORDER BY isnull([Submission Date] ,cast('1770/01/01' as datetime)) ASC

回答8:

I know this is old, but when I found it I noticed the accepted solution, https://stackoverflow.com/a/821856/7177892, could be simplified by making the result of the CASE statement be either today (GETDATE()) or the actual date.

Original:

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

Simplified:

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