Menu
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
917
Summary. Use the ORDER BY clause to sort the result set by one or more columns. Use the ASC option to sort the result set in ascending order and the DESC option to sort the result set in descending order. The ORDER BY clause is evaluated after the FROM and SELECT clauses.
Using ORDER BY to sort on two columns In such a case, MySQL treats the first field as primary and the latter as secondary. Therefore, it first sorts the primary and then the second one. Hence, in this example, we'll demonstrate ORDER BY on two columns, one field as ASC, and another DESC.
Using with multiple columnsDefine your multiple column names in ORDER BY clause separated by a comma (,). You can also specify your sorting order ASC or DESC . In the above query, I am ordering the emp_salary table by age in Ascending order and salary by descending order.
When sorting your result set using the SQL ORDER BY clause, you can use the ASC and DESC attributes in a single SELECT statement. This example would return the records sorted by the category_id field in descending order, with a secondary sort by product_name in ascending order.
SELECT *
FROM mytable
ORDER BY
GREATEST(
COALESCE(time1, 0),
COALESCE(time2, 0),
COALESCE(time3, 0)
) DESC
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
