选择其中一列值不同的 3 个最近的记录 [英] Select the 3 most recent records where the values of one column are distinct

问题描述

我有下表:

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

我想要做的是选择最近的 3 条记录(按时间降序)，它们的 otheridentifier 是不同的.所以在这种情况下，结果将是 id 的:5、4 和 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 将被跳过，因为有一个更新的记录具有相同的 otheridentifier 字段.

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

这是我尝试做的:

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

但是，我最终得到了 id = 5、3 和 1 的行，而不是预期的 5、4、2.

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

谁能告诉我为什么这个查询不会返回我期望的结果?我尝试将 ORDER BY 更改为 ASC，但这只是将返回的行重新排列为 1、3、5.

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.

推荐答案

它不会返回您期望的内容，因为分组发生在排序之前，正如 SQL 语句中子句的位置所反映的那样.不幸的是，您将不得不变得更漂亮才能获得所需的行.试试这个:

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

这篇关于选择其中一列值不同的 3 个最近的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！