ORDER BY DATE showing NULLS first then most recent dates

user_48478 0
Viewed 74739

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?

sql-server-2000 sql-server
9 Answers

@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.

user_22300

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

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

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.

user_151680

try

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

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.

user_37261570

try this

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

user_867290

Assuming Submission Date less than current date-time

SELECT a,b,c,[Submission Date]
FROM someView
ORDER BY ISNULL([Submission Date], GETDATE()) DESC
user_22942080
Related