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?

Chromatograph

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.

Kandis

Why bother with DESC? Just switch your 0 and 1. – Staminody

Clearly, but I was trying to keep my edits as close to Chris' suggestion as possible. – Kandis

I actually didn't look at the tags to see it was for sql2000. Mine should work in MySQL. :) – Psychosocial

@Chris: yeah, I've done that before. Why don't you edit your post to note that? Nothing wrong with posting the syntax under other flavors of SQL. – Kandis

Thanks for the answer. I got it to work by using this solution, please edit your code to look like this: ORDER BY (CASE WHEN [Submission Date] IS NULL THEN 1 ELSE 0 END) DESC, [Submission Date] DESC – Chromatograph

Eppz, I changed the code as you requested. BTW, it would be Ok for you to tweak my posting yourself to fit your needs. I certainly wouldn't mind and it's well within the spirit of SO. – Kandis

@Euro Micelli, I don't have a high enough rep to edit your post yet. So please vote up my question <3 – Chromatograph

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

Psychosocial

+1 this was the only answer that worked in Access SQL for me. – Precipitation

is there any reason you have the square parentheses? – Spasmodic

@Spasmodic Square parentheses help when the column name has an embedded balnk – Bock

@Bock blank?? you mean like a whitespace e.g. "column name"? – Spasmodic

@Spasmodic yes, if you have a column name like "My Column" you need square parentheses to access it: SELECT [My Column] .... – Thirtytwo

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

Cutlet

postgres supports this by now – Panda

"Yet" comments can be accurate when written and less accurate a dozen years later. – Cutlet

Answer 4

try

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

Campbellbannerman

9999/99/99 with throw an error as it cannot be parsed to a date. – Chromatograph

I would use this solution instead of the case. – Ryun

Can easily use in LINQ – Selenite

Answer 5

OrderBy="ColumnName = NULL desc, ColumnName desc"

Casmey

Answer 6

I know this is old, but when I found it I noticed the accepted solution, https://mcmap.net/q/293546/-order-by-date-showing-nulls-first-then-most-recent-dates, 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

Aggression

This simplification won't work as-is if you have records with today's date or future dates. In that case, you could change from GETDATE() to DATEADD() and add some obscenely large number of days or years to the current date. – Aggression

Answer 7

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.

Propitiatory

Answer 8

try this

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

Forelock

Answer 9

Assuming Submission Date less than current date-time

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

Oliva