Sky Sky - 5 months ago 8
MySQL Question

mysql order by two unixtime columns combine

Table

user
structure:

id(PK)(int)
name(varchar)
time1(int10) unixtime
time2(int10) unixtime

SELECT * FROM `user` ORDER BY `time1` DESC,`time2` DESC


+------------------------------------------------------+
| id | name | time1 | time2 |
+------------------------------------------------------+
| 12345 | Joe | 1405605785 | 1406733506 |
| 12346 | John | 1406733506 | |
| 12347 | David | | 1405684190 |
+------------------------------------------------------+


I am using this sql:

SELECT * FROM `user` ORDER BY `time1` DESC,`time2` DESC


How to combine two columns time to sort desc?

============

more example reply for sagi

+------------------------------------------------------+
| id | name | time1 | time2 |
+------------------------------------------------------+
| 12345 | Joe | 1 | 2 |
| 12346 | John | 5 | |
| 12347 | David | | 4 |
+------------------------------------------------------+


I want to sort like that (DESC)

John (time = 5)
David (time = 4)
Joe (time max value = 2)

Answer

This is called conditional ordering using CASE EXPRESSION :

SELECT * FROM `user` t
ORDER BY CASE WHEN t.time1 is null THEN t.time2 ELSE t.time1 END DESC

Could also be written as:

ORDER BY coalesce(t.time1,t.time2)

You didn't provide any expected results, and you didn't say by which column the result should be ordered in case both time1,time2 are not NULL , so I assumed you want it to be ordered by the first column. If that's not the case, replace time1 with time2 .

Try this:

ORDER BY GREATEST(t.time1,t.time2) DESC
Comments