Ordering by multiple fields in SQLite.

Question

I have a simple table, something like: int id, date created_at, date updated_at. I'd like to order the rows so that any rows that have an updated_at will be sorted by that, and any that don't will be sorted by the created_at. The problem is that something like:

SELECT * FROM table ORDER BY updated_at, created_at

doesn't work. I've been looking at custom order clauses, something like:

ORDER BY CASE WHERE updated_at ISNULL <do something> END, created_at

but can't seem to get anything to work. Help?

EDIT: I should have specified that I want the updated_at fields to sort before the created_at fields. So, if the data looks like:

id  created_at  updated_at
--  ----------  ----------
1   2009-01-08  null
2   2009-09-08  null
3   2009-07-02  null
4   2009-09-05  2009-09-06
5   2009-04-01  null
6   2009-09-07  2009-09-08

I'd want results like:

id  created_at  updated_at
--  ----------  ----------
6   2009-09-07  2009-09-08
4   2009-09-05  2009-09-06
2   2009-09-08  null
3   2009-07-02  null
5   2009-04-01  null
1   2009-01-08  null

Answer 1

looks like you're looking for:

ORDER BY COALESCE(updated_at, created_at)

per the docs, coalesce returns

a copy of the first non-NULL argument. If all arguments are NULL then NULL is returned. There must be at least 2 arguments.

Edit: given the OP's edit, he may instead want:

ORDER BY COALESCE(updated_at, '1929-9-9') DESC, created_at DESC

or similar tricks for DESC ordering; or maybe

ORDER BY MAX(COALESCE(updated_at, '1929-9-9'), created_at) DESC

it's impossible to tell exactly what the OP wants from a single and very peculiar example, but a variant on some one of these approaches should solve his actual problem, whatever that problem may actually be in specific, precise, and complete detail.

Answer 2

IFNULL, see here

In this instance you'd use something like:

IFNULL(updated_at, '1-jan-2100'), created_at

All columns without an updated_at field would be given the default sort order and would then sort by created_at. The value of 1-jan-2100 ensures they appear last in the list. Change this to be 1900 if you want them first.

Answer 3

Ordering so nulls are at the bottom, use DESC:

ORDER BY t.created_at, t.updated_at DESC