ORDER BY DATE showing NULLS first then most recent dates

Question

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?

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

Answer 2

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

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