Select the 3 most recent records where the values of one column are distinct

Question

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 otheridentifiers 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.

Answer 1

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 

Answer 2

You could join the table on itself to filter the last entry per otheridentifier, and then take the top 3 rows of that:

SELECT last.* FROM `table` last LEFT JOIN `table` prev      ON prev.`otheridentifier` = last.`otheridentifier`     AND prev.`time` < last.`time` WHERE prev.`id` is null ORDER BY last.`time` DESC  LIMIT 3