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

问题描述

我在留言簿中有一张带有用户评论的表格.列为:id，user_id，标题，评论，时间戳.

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

我需要为每个用户选择最新行. 我尝试使用分组依据进行此操作，但尚未对其进行管理，因为我无法在同一查询中选择按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，lipte和注释.该怎么办?

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