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

问题描述

我有下表：

  id时间文字otheridentifier 
 --------- ---------------------------------- 
 1 6 apple 4 
 2 7 orange 4 
 3 8香蕉3 
 4 9梨3 
 5 10葡萄2 
  

我想要做的是选择3个最近的记录（按时间desc），其 otheridentifier s是不同的。因此，在这种情况下，结果将是 id 的：5,4和2。

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

下面是我试图做的：

  SELECT * FROM`table` GROUP BY（`otheridentifier` ）ORDER BY`time` DESC LIMIT 3 
  

然而，我最终得到 id = 5， 3 和 1 ，而不是像预期的那样使用5,4,2。

有人能告诉我为什么这个查询不会返回我预期的结果吗？我尝试将ORDER BY更改为ASC，但是这只是将返回的行重新排列为1,3,5。 t返回您期望的结果，因为在排序之前会发生分组，这反映在SQL语句中子句的位置。不幸的是你不得不花更多的时间来获得你想要的行。试试这个：

$ p $ 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


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.

解决方案

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屋！