MySQL Order by multiple column combined (not order by field1 asc, field2 asc)

Question

Hi all, it seems like a typical question but it's different.

I have a table with an id and 3 timestamp fields (to simply). Initially all 3 fields are null, and they get filled with values. Examples of rows are:

id time1      time2      time3
1  1259625661 1259643563 null
2   null      1259621231 null
3  1259625889 null       1259644511
4   null      1259621231 null
5   null      null       1259644511
6   null      1259621231 null
7  1259625889 null       null

What I need is to get a list of the id's sorted by the most recent timestamp (ignoring if it's in time1, time2 or time3). Doing a order by time1 desc, time2 desc, time3 desc gives me a wrong list, as it first sorts all the time1 field, then the second, etc...

Expected result is a list of id's.

That can be done in MySQL in a single query? Thanks

Answer 1

SELECT  *
FROM    mytable
ORDER BY
        GREATEST(
        COALESCE(time1, 0),
        COALESCE(time2, 0),
        COALESCE(time3, 0)
        ) DESC