CODEWITHSUNDEEP
sql-servert-sqlsql-order-byisnull
Myurathan Kajendran
Myurathan Kajendran

Reputation: 677

TSQL ORDER BY with nulls first or last (at bottom or top)

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?

Upvotes: 51

Views: 44436

Answers (5)

rjose
rjose

Reputation: 615

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

Upvotes: 0

David C Holley
David C Holley

Reputation: 19

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'),

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81970

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')

Upvotes: 19

Tez Kurmala
Tez Kurmala

Reputation: 61

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.

Upvotes: 3

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

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

Upvotes: 88

Related Questions