选择3个最近的记录，其中一个列的值是不同的。

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.

我要做的是选择3个最近的记录(通过时间desc)，它的其他标识符是不同的。在这种情况下，结果是id: 5 4 2。

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

id = 3将被跳过，因为有一个最近的记录具有相同的otheridentifier字段。

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.

然而，我最终得到id为5、3和1的行，而不是预期的5、4、2。

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.

有人能告诉我为什么这个查询不会返回我所期望的吗?我试着将订单更改为ASC，但这只是将返回的行重新排列为1、3、5。

8 个解决方案

#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:

它不会返回您所期望的，因为分组发生在排序之前，这反映在SQL语句中子句的位置上。不幸的是，要得到你想要的行，你必须变得更漂亮。试试这个:

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

#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

#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 you can still do a GROUP BY after and ORDER BY like this:

我也有类似的要求，但我有更高级的选择标准。使用其他的一些答案，我不能确切地得到我需要的，但我发现你仍然可以做一个小组后，然后这样排序:

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

#4

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

#5

Andomar's answer is probably best in that it doesn't use a subquery.

Andomar的回答可能是最好的，因为它不使用子查询。

An alternative approach:

另一种方法:

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

#6

You can use this query to get correct answer:

你可以用这个查询得到正确的答案:

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

#7

what about

是什么

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

#8

This also:

这也:

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.

#1