SQL sort ascending but null values descending

Question

Table has a nullable ContactDate field. I want to sort these records so that non-null values come first in ascending order and then null values are sorted after non-nullable values.

Select * from myTable Order by ContactDate ASC

returns following

NULL
NULL
NULL
NULL
2015-07-27 10:00:00.000
2015-07-29 10:00:00.000

then,

Select * from myTable Order by ContactDate DESC

returns following

2015-07-29 10:00:00.000
2015-07-27 10:00:00.000
NULL
NULL
NULL
NULL

But I need it like this:

2015-07-27 10:00:00.000 -- asc
2015-07-29 10:00:00.000 -- asc
NULL
NULL
NULL
NULL

Using MS SQL Server 2012

Try `ORDER BY ContactDate ASC NULLS LAST`. That works in postgresql, not sure about sql-server. — Colonel Thirty Two, Jul 29 '15 at 14:05

Ullas · Accepted Answer · 2015-07-29T14:12:46.770

5

Use a case statement in order by.

Query

select * from myTable
order by case when ContactDate is null then 1
else 0 end,ContactDate;

Fiddle demo

edited Jul 29 '15 at 14:12

answered Jul 29 '15 at 14:07

Ullas

11,201
4
31
47

Why downvote?? What's wrong with my query? – Ullas Jul 29 '15 at 14:14

fez · Answer 2 · 2015-07-29T14:35:55.970

0

You could use ISNULL() to convert the NULL values to 9999-12-31 and then order by ContactDate:

SELECT * FROM myTable
ORDER BY ISNULL(CONVERT(VARCHAR, ContactDate), '9999-12-31') ASC

edited Jul 29 '15 at 14:35

answered Jul 29 '15 at 14:24

fez

1,628
2
20
31

SQL sort ascending but null values descending

2 Answers2