49

I have a date column which has some NULL. I want to order by the date column ASC, but I need the NULL s to be at the bottom. How to do it on TSQL?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Myurathan Kajendran
  • 647
  • 3
  • 8
  • 15
  • 3
    Possible duplicate of [SQL Server ORDER BY date and nulls last](http://stackoverflow.com/questions/5886857/sql-server-order-by-date-and-nulls-last) – HABO Mar 24 '17 at 13:18

5 Answers5

85

In standard SQL you can specify where to put nulls:

order by col asc nulls first
order by col asc nulls last
order by col desc nulls first
order by col desc nulls last

but T-SQL doesn't comply with the standard here. The order of NULLs depends on whether you sort ascending or descending in T-SQL:

order by col asc -- implies nulls first
order by col desc -- implies nulls last

With integers you could simply sort by the negatives:

order by -col asc -- sorts by +col desc, implies nulls first
order by -col desc -- sorts by +col asc, implies nulls last

But this is not possible with dates (or strings for that matter), so you must first sort by is null / is not null and only then by your column:

order by case when col is null then 1 else 2 end, col asc|desc -- i.e. nulls first
order by case when col is null then 2 else 1 end, col asc|desc -- i.e. nulls last
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • 14
    +1 for calling out the standard vs. TSQL difference. `order by col asc` gives nulls first and `order by col desc` gives nulls last because SQL Server treats Null values as the lowest possible values, see [SELECT - ORDER BY Clause](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql) – HappyTown Mar 24 '17 at 22:20
  • 3
    Love the sorting by negatives for integers! – Simon Hewitt Apr 04 '19 at 05:44
  • 2
    It should be mentioned that the `case when` solution significantly negatively affects performance and should not be performed on large datasets. ([source](https://www.dba-presents.com/index.php/databases/sql-server/36-order-by-and-nulls-last-in-sql-server)) – DerSeegler Jan 03 '20 at 11:41
  • 2
    When doing the same for strings anyone think of anything nicer than: `CASE WHEN col IS NULL THEN 'ZZZZZZZ' ELSE col END` – Dave Potts Apr 06 '20 at 18:59
  • Instead of `case when col is null then 1 else 2 end` the shorter `IIF(col IS NULL, 0, 1)` is also possible. – slartidan Jun 17 '21 at 09:23
18
Select *
 From  YourTable
 Order By case when DateCol is null then 0 else 1 end
         ,DateCol

Or even Order By IsNull(DateCol,'2525-12-31')

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
3

order by case when col_name is null then 1 else 2 end, col_name asc did the trick on Oracle. However the same on MS SQL Server pushes the NULL records down leaving non null to be on top of the result set.

Tez Kurmala
  • 89
  • 5
1

This did the trick for me just now. Fortunately, I'm working with text. For anything numeric, I'd probably go with all 9's. COALESCE(c.ScrubbedPath,'zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz'),

David C Holley
  • 19
  • 1
  • 1
    The original question is about sorting `date` values, not text, nor numeric values. Also, a generic, always applicable solution is usually preferred over hardcoded values like `zzzzz...`. – Sander Sep 09 '20 at 19:56
  • @Sander - Based on the question title, it is not unreasonable to treat the question more generically - how to sort nulls first or last, and the details about sorting by date are just an example. – dbenham Aug 19 '21 at 14:54
0

Sometimes, you may need to use a subquery to get this right:

 select site_id, site_desc
    from (
    select null as site_id, 'null' as site_desc
    union
    select s.site_id,
        s.site_code+'--'+s.site_description as site_desc
    from site_master s with(nolock)
    )x
    order by (case when site_id is null then 0 else 1 end), site_desc
rjose
  • 557
  • 5
  • 13