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?

Euro Micelli · Accepted Answer · 2009-05-05T16:26:09.023

120

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

edited May 05 '09 at 16:26

answered May 04 '09 at 20:21

Euro Micelli

33,285
8
51
70

4

Why bother with DESC? Just switch your 0 and 1. – Joel Coehoorn May 04 '09 at 20:26
Clearly, but I was trying to keep my edits as close to Chris' suggestion as possible. – Euro Micelli May 04 '09 at 20:37
I actually didn't look at the tags to see it was for sql2000. Mine should work in MySQL. :) – Chris Bartow May 04 '09 at 21:17
@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. – Euro Micelli May 04 '09 at 21:22
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 – Eppz May 05 '09 at 12:55
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. – Euro Micelli May 05 '09 at 16:29
@Euro Micelli, I don't have a high enough rep to edit your post yet. So please vote up my question <3 – Eppz May 05 '09 at 17:20

score 28 · Answer 2 · answered May 04 '09 at 20:17

28

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

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

answered May 04 '09 at 20:17

Chris Bartow

14,873
11
43
46

+1 this was the only answer that worked in Access SQL for me. – Drew Chapin Jun 10 '13 at 14:13
is there any reason you have the square parentheses? – AndrewBramwell May 11 '16 at 12:46
2

@AndrewBramwell Square parentheses help when the column name has an embedded balnk – brewmanz Jan 09 '17 at 02:37
@brewmanz blank?? you mean like a whitespace e.g. "column name"? – AndrewBramwell Jan 09 '17 at 18:55
1

@AndrewBramwell yes, if you have a column name like "My Column" you need square parentheses to access it: SELECT [My Column] .... – Eric Draven May 18 '17 at 09:39

score 20 · Answer 3 · answered May 04 '09 at 20:27

20

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.

answered May 04 '09 at 20:27

Jonathan Leffler

730,956
141
904
1,278

postgres supports this by now – Bensge Aug 17 '21 at 14:37
"Yet" comments can be accurate when written and less accurate a dozen years later. – Jonathan Leffler Aug 17 '21 at 14:41

Sadegh · Answer 4 · 2009-05-04T20:27:22.540

3

try

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

edited May 04 '09 at 20:27

answered May 04 '09 at 20:14

Sadegh

6,654
4
34
44

9999/99/99 with throw an error as it cannot be parsed to a date. – Eppz May 04 '09 at 20:19
I would use this solution instead of the case. – Ionic Jun 28 '15 at 20:06
Can easily use in LINQ – Reza F.Rad Sep 02 '16 at 18:33

score 1 · Answer 5 · answered Jun 28 '15 at 15:23

1

OrderBy="ColumnName = NULL desc, ColumnName desc"

answered Jun 28 '15 at 15:23

Paul

115
1
3

score 1 · Answer 6 · edited May 23 '17 at 12:34

1

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

edited May 23 '17 at 12:34

Community

1
1

answered Nov 18 '16 at 11:30

nconantj

11
2

2

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. – nconantj Nov 18 '16 at 11:38

score 1 · Answer 7 · edited Aug 07 '19 at 06:29

1

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.

edited Aug 07 '19 at 06:29

cs95

379,657
97
704
746

answered Aug 06 '19 at 16:20

Marco Leite

88
1
1
7

han · Answer 8 · 2021-07-24T16:09:26.530

0

Assuming Submission Date less than current date-time

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

edited Jul 24 '21 at 16:09

answered Jul 24 '21 at 15:43

han

118
1
11

littlechris · Answer 9 · 2009-05-04T20:25:22.030

0

try this

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

edited May 04 '09 at 20:25

answered May 04 '09 at 20:19

littlechris

4,174
10
43
66

ORDER BY DATE showing NULLS first then most recent dates

9 Answers9

Linked

Related