Sorting nearest date with MySQL

Question

I have a simple task list which contains amongst others a title field and a deadline field. Some tasks got deadlines, other don't. Deadlines are simply stored with a MySQL date type.

But the tough part (for me anyway) is to sort the tasks in the correct order:

1. Ascending deadlines
2. The rest (maybe by ID, but not important)

Current query:

SELECT * FROM tasks ORDER BY deadline ASC

The deadline field is '0000-00-00' by default, so this query will show the tasks in the following order:

1. Tasks with no deadlines
2. Ascending deadlines

Can anybody point me in the right direction, or do I need to do multiple queries?

Answer 1

One obvious possibility would be (in the application that creates the projects) set a deadline way in the future (say '9999-12-31') if the user doesn't enter a deadline. Of course, it's not clear whether you can change that code or not...

Answer 2

SELECT * FROM tasks
ORDER BY CASE WHEN deadline = '0000-00-00'
              THEN '9999/09/09' 
              ELSE deadline 
         END ASC

When sorting, replace '0000-00-00' by a 'big' datetime value: '9999/09/09'.

Answer 3

Is it really 0000-00-00? The docs says:

"The supported range is '1000-01-01' to '9999-12-31'. "

I would consider storing NULL in deadline for those that do not have one.

SELECT * FROM tasks ORDER BY ifnull(deadline, '9999-12-31') ASC

Answer 4

use a where to hide your tasks without a deadline:

SELECT * FROM tasks WHERE deadline != '0000-00-00' ORDER BY deadline

Answer 5

might be more performant(could use your index on deadline field, putting a CASE WHEN on ORDER BY clause might not use your index on deadline field):

SELECT 1 /* no deadlines first */ as sort, * 
FROM tasks 
WHERE deadline = '0000-00-00' OR deadline IS NULL -- no deadlines
UNION
SELECT 2 /* the rest, with deadlines */ as sort, * 
FROM tasks 
WHERE deadline <> '0000-00-00' -- with deadlines

ORDER BY sort, deadline -- this will apply to both results

or if you want najmeddine concise query, improve his/her query, use this mysqlism to make it shorter:

SELECT * FROM tasks
ORDER BY deadline <> '0000-00-00' /* no deadlines first */, deadlines