从Oracle选择每个组的最新行

Question

我有一个带有用户留言的表格，列包括：id、user_id、title、comment、timestamp。

我需要选择每个用户最新的一行。我尝试使用group by来完成，但由于不能在同一查询中选择其他内容，所以没有成功。

SELECT user_id, MAX(ts) FROM comments GROUP BY user_id

比如在这个查询中，我无法添加同时选择id、tilte和comment列。该如何实现？

- user1985273

Answer 1

您可以使用分析函数

SELECT *
  FROM (SELECT c.*,
               rank() over (partition by user_id order by ts desc) rnk
          FROM comments c)
 WHERE rnk = 1

根据您对于如何处理并列行的需求（如果有两行具有相同的user_id和ts），您可能需要使用row_number或dense_rank函数，而不是使用rank函数。 当存在并列行时，rank函数会允许多个行处于第一位置。row_number函数将任意返回一行作为第一位置。dense_rank函数将在多个行并列第一时，行为类似于rank函数，但会将下一行视为第二而不是第三。

- Justin Cave

Answer 2

您可以使用 JOIN 来扩展查询：

select c.*
from comments c join
     (select user_id, max(ts) as maxts
      from comments c2
      group by user_id
     ) cc
     on c.user_id = cc.user_id and c.ts = cc.maxts;

还有其他方法。通常的建议是使用row_number()：

select t.*
from (select c.*, row_number() over (partition by user_id order by ts desc) as seqnum
      from comments c
     ) c
where seqnum = 1;

这两个查询在细微之处有所不同。如果用户的最新评论恰好具有相同的ts，第一个查询将返回重复行。而第二个查询每个用户只返回一行。

- Gordon Linoff

Answer 3

使用dense rank first/last函数可以非常简单且高效地解决这类问题：

select id,
       max(user_id) keep (dense_rank last order by ts) over (partition by id) as user_id,
       max(title)   keep (dense_rank last order by ts) over (partition by id) as title,
       max(comment) keep (dense_rank last order by ts) over (partition by id) as comment,
       max(ts)                                                                as ts
from   comments;

- user5683823