ash ash - 5 months ago 18
MySQL Question

MySQL Group By ordering

I have the following table:

id time text otheridentifier
-------------------------------------------
1 6 apple 4
2 7 orange 4
3 8 banana 3
4 9 pear 3
5 10 grape 2


What I want to do is select the 3 most recent records (by time desc), whose
otheridentifier
s are distinct. So in this case, the result would be
id
's: 5, 4, and 2.

id
= 3 would be skipped because there's a more recent record with the same
otheridentifier
field.

Here's what I tried to do:

SELECT * FROM `table` GROUP BY (`otheridentifier`) ORDER BY `time` DESC LIMIT 3


However, I end up getting rows of
id
= 5, 3, and 1 instead of 5, 4, 2 as expected.

Can someone tell me why this query wouldn't return what I expected? I tried changing the ORDER BY to ASC but this simply rearranges the returned rows to 1, 3, 5.

Thanks for your help!

Answer

It doesn't return what you expect because grouping happens before ordering, as reflected by the position of the clauses in the SQL statement. You're unfortunately going to have to get fancier to get the rows you want. Try this:

SELECT *
FROM `table`
WHERE `id` = (
    SELECT `id`
    FROM `table` as `alt`
    WHERE `alt`.`otheridentifier` = `table`.`otheridentifier`
    ORDER BY `time` DESC
    LIMIT 1
)
ORDER BY `time` DESC
LIMIT 3