Select the last 3 entries in which the values ​​of one column differ

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 last 3 entries (by time) whose otheridentifier is different. Thus, in this case, the result will be id s: 5, 4, and 2.

id = 3 will be skipped because there is a later entry with the same otheridentifier field.

Here is what I tried to do:

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

However, in the end I get the lines id = 5, 3 and 1 instead of 5, 4, 2, as expected.

Can someone tell me why this query will not return what I expected? I tried changing ORDER BY to ASC, but it just permutes the returned rows to 1, 3, 5.

Answer 1

It does not return what you expect, because the grouping occurs before ordering, which is reflected in the position of the sentences in the SQL statement. Unfortunately, you will have to love you in order to get the lines you need. Try the following:

 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 can join the table directly to filter the last record on the otheridentifier , and then take the top 3 rows of this:

 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 

Answer 3

I had a similar requirement, but I had more advanced selection criteria. Using some of the other answers, I couldn't get exactly what I needed, but I found that you can still do GROUP BY after and ORDER BY as follows:

 SELECT t.* FROM (SELECT * FROM table ORDER BY time DESC) t GROUP BY t.otheridentifier 

Answer 4

 SELECT * FROM table t1 WHERE t1.time = (SELECT MAX(time) FROM table t2 WHERE t2.otheridentifier = t1.otheridentifier) 

Answer 5

Andomar’s answer is probably best in that it does not use a subquery.

Alternative approach:

 select * from `table` t1 where t1.`time` in ( select max(s2.`time`) from `table` t2 group by t2.otheridentifier ) 

Answer 6

You can use this query to get the correct answer:

 SELECT * FROM (SELECT * FROM `table` order by time DESC) t group by otheridentifier 

Answer 7

What about

 SELECT *, max(time) FROM `table` group by otheridentifier 

Answer 8

It's the same:

 SELECT * FROM OrigTable T INNER JOIN ( SELECT otheridentifier,max(time) AS duration FROM T GROUP BY otheridentifier) S ON S.duration = T.time AND S.otheridentifier = T.otheridentifier.