Question
Select the 3 most recent records where the values of one column are distinct
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.