oracle 如何获得Oracle中每个组的最大值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40941252/
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
How to get the max value for each group in Oracle?
提问by user7243231
I've found some solutions for this problem, however, they don't seem to work with Oracle.
我为这个问题找到了一些解决方案，但是，它们似乎不适用于 Oracle。
I got this:
我懂了：
I want a view to present only the informations about the oldest person for each team. So, my output should be something like this:
我想要一个视图，只显示每个团队最年长的人的信息。所以，我的输出应该是这样的：
PERSON | TEAM | AGE
Sam | 1 | 23
Michael | 2 | 21
How can I do that in Oracle?
我怎样才能在 Oracle 中做到这一点？
回答by Gordon Linoff
One method uses keep:
一种方法使用keep：
select team, max(age) as age,
max(person) keep (dense_rank first order by age desc) as person
from t
group by team;
There are other methods, but in my experience, keepworks quite well.
还有其他方法，但根据我的经验，keep效果很好。
回答by Nikita Kotlyarov
Here is an example without keepbut with row_number():
这是一个没有keep但有的例子row_number()：
with t0 as
(
select person, team, age,
row_number() over(partition by team order by age desc) as rn
from t
)
select person, team, age
from t0
where rn = 1;
回答by arturro
select * from table
where (team, age) in (select team, max(age) from table group by team)
回答by Adwitiya Kumar
select * from (select person,team,age,
dense_rank() over (partition by team order by age desc) rnk)
where rnk=1;
