从 oracle 为每个组选择最新的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40404497/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select latest row for each group from oracle
提问by user1985273
I have a table with user comments in a guestbook. Columns are: id, user_id, title, comment, timestamp.
我在留言簿中有一张包含用户评论的表格。列是：id、user_id、title、comment、timestamp。
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:
我需要为每个用户选择最新的行。我曾尝试使用 group by 执行此操作，但尚未对其进行管理，因为我无法在按 user_id 分组的同一查询中选择任何其他内容：
SELECT user_id, MAX(ts) FROM comments GROUP BY user_id
for example in this query i cant add to also select columns id, tilte and comment. How can this be done?
例如，在此查询中，我无法添加到还选择列 id、tilte 和 comment。如何才能做到这一点？
采纳答案by Justin Cave
You can use analytic functions
您可以使用分析函数
SELECT *
FROM (SELECT c.*,
rank() over (partition by user_id order by ts desc) rnk
FROM comments c)
WHERE rnk = 1
Depending on how you want to handle ties (if there can be two rows with the same user_idand ts), you may want to use the row_numberor dense_rankfunction rather than rank. rankwould allow multiple rows to be first if there was a tie. row_numberwould arbitrarily return one row if there was a tie. dense_rankwould behave like rankfor the rows that tied for first but would consider the next row to be second rather than third assuming two rows tie for first.
根据您希望如何处理关系（如果可以有两行具有相同的user_id和ts），您可能希望使用row_numberordense_rank函数而不是rank。 rank如果有平局，将允许多行排在第一位。 row_number如果有平局，将任意返回一行。 dense_rank会像rank第一行并列的行一样，但会认为下一行是第二行而不是第三行，假设两行并列第一。
回答by Gordon Linoff
You can build on your query using a JOIN:
您可以使用以下命令构建查询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;
There are other ways. Typical advice is to use row_number():
还有其他方法。典型的建议是使用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;
These two queries are subtly different. The first will return duplicates if the most recent comment for a user had exactly the same ts. The second returns one row per user.
这两个查询略有不同。如果用户的最新评论完全相同，则第一个将返回重复项ts。第二个返回每个用户一行。
回答by mathguy
This type of problems has a very simple and very efficient solution with the dense rank first/lastfunction:
这种类型的问题有一个非常简单且非常有效的解决方案，具有以下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;
