ansaurus

Question

SQL how to make null values come last when sorting ascending

Answer 1

+7 A:

order by coalesce(date-time-field,large date in future)

Gratzy 2009-09-30 14:58:13

While this will generally work, it should be noted this answer has a few issues: large date in future may collide with or be less than actual data, resulting in an imperfect sort. Also, it is a "magic number" solution that is not self-documenting.

RedFilter 2009-09-30 17:48:28

Answer 2

+6 A:

select MyDate
from MyTable
order by case when MyDate is null then 1 else 0 end, MyDate

RedFilter 2009-09-30 14:59:04

This will make all of the not null date fields equal as "0" and the null fields "1" so the nulls will come after the not nulls but the not nulls won't be sorted.

Gratzy 2009-09-30 15:06:40

@Gratzy: The null dates are ordered with a value *higher* than those that are **not null**, so the null values will appear at the end - it is a valid, correct answer.

OMG Ponies 2009-09-30 17:37:31

@Gratzy: Note the second sort clause: the NOT NULL dates will come first as they all will get sorted by 0. Then, the second sort clause takes effect and they will all get ordered by MyDate.

RedFilter 2009-09-30 17:46:35

@OrbMan I apologize I didn't see the second order by my bad

Gratzy 2009-09-30 17:59:28

+1 Nifty trick (and just what I need) :-).

sleske 2010-03-22 10:22:24

ansaurus

tags:

views:

answers:

SQL how to make null values come last when sorting ascending

related questions