从 oracle 为每个组选择最新的行 [英] Select latest row for each group from oracle

问题描述

我在留言簿中有一张包含用户评论的表格.列是:id、user_id、title、comment、timestamp.

I have a table with user comments in a guestbook. Columns are: id, user_id, title, comment, timestamp.

我需要为每个用户选择最新的行.我曾尝试使用 group by 执行此操作，但尚未对其进行管理，因为我无法在按 user_id 分组的同一查询中选择任何其他内容:

I need to select the latest row for each user. I have tried to do this with group by but havent managed it because i cant select anything else in the same query where i group by user_id:

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

例如在这个查询中，我不能添加到选择列 id、tilte 和 comment.这怎么办?

for example in this query i cant add to also select columns id, tilte and comment. How can this be done?

推荐答案

可以使用解析函数

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，但会认为下一行是第二行，而不是第三行，假设两行并列第一.

Depending on how you want to handle ties (if there can be two rows with the same user_id and ts), you may want to use the row_number or dense_rank function rather than rank. rank would allow multiple rows to be first if there was a tie. row_number would arbitrarily return one row if there was a tie. dense_rank would behave like rank for the rows that tied for first but would consider the next row to be second rather than third assuming two rows tie for first.

这篇关于从 oracle 为每个组选择最新的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！