Possible to use SQL to sort by date but put null dates at the back of the results set?

Question

I have a bunch of tasks in a MySQL database, and one of the fields is "deadline date". Not every task has to have to a deadline date.

I'd like to use SQL to sort the tasks by deadline date, but put the ones without a deadline date in the back of the result set. As it is now, the null dates show up first, then the rest are sorted by deadline date earliest to latest.

Any ideas on how to do this with SQL alone? (I can do it with PHP if needed, but an SQL-only solution would be great.)

Thanks!

Answer 1

select foo, bar, due_date,
order by case isnull(due_date, 0) when 0 then 1 else 0 end, due_date

So you have 2 order by clauses. The first puts all non-nulls in front, then sorts by due date after that

Answer 2

SELECT * FROM myTable
WHERE ...
ORDER BY ISNULL(myDate), myDate

Answer 3

Here's a solution using only standard SQL, not ISNULL(). That function is not standard SQL, and may not work on other brands of RDBMS.

SELECT * FROM myTable
WHERE ...
ORDER BY CASE WHEN myDate IS NULL THEN 1 ELSE 0 END, myDate;