Saturday, March 22, 2008

MySQL ORDER BY multiple columns

Given this MySQL query where the single letter column names relate to the format argument of the PHP date function,

SELECT Y,n,j,G
FROM my_table
ORDER BY Y,n,j,G DESC
LIMIT 1


One might assume the most recent row of the following two would be returned.
In case you don't know these off the top of your head the order is,
hour, day, month, year

INSERT INTO my_table (G, j, n, Y) VALUES 
(23, 27, 10, 2007),
(0, 28, 10, 2007)


However that's not the case & the earlier of the two is actually returned.

Reason being that when a MySQL result is being ordered by multiple columns, the ASC or DESC keyword needs to be specified after each of the column names.

So the following query would return the later of the two rows.

SELECT Y,n,j,G
FROM my_table
ORDER BY Y DESC,n DESC,j DESC,G DESC
LIMIT 1