英文:
Select max from groups in SQL
问题
SELECT
NAME,
THING,
MAX(SUM(NUM))
FROM this_table
GROUP BY NAME, THING;
英文:
I have a table similar to the following:
NAME,THING,NUM
abc,thing1,9
abc,thing1,1
abc,thing2,3
abc,thing2,1
jkl,thing1,88
jkl,thing1,12
jkl,thing2,13
jkl,thing2,14
xyz,thing1,37
xyz,thing1,3
xyz,thing2,101
xyz,thing2,99
How do I query this to find the highest ranking total for the second column for each value in the first column? Such as:
abc,thing1,10
jkl,thing1,100
xyz,thing2,200
This query:
SELECT
NAME,
THING,
SUM(NUM)
FROM this_table
GROUP BY NAME, THING;
yields this result:
abc,thing1,10
abc,thing2,4
jkl,thing1,100
jkl,thing2,27
xyz,thing1,40
xyz,thing2,200
but I just want the highest value for each name and thing.
答案1
得分: 3
你没有提到数据库类型，所以我会假设是 PostgreSQL。你可以执行以下操作：
select *
from (
select name, thing, sum(num) as s,
row_number() over(partition by name order by sum(num) desc) as rn
from t
group by name, thing
) x
where rn = 1
在 db fiddle 上查看运行示例。
英文:
You don't mention the database, so I'll assume it's PostgreSQL. You can do:
select *
from (
select name, thing, sum(num) as s,
row_number() over(partition by name order by sum(num) desc) as rn
from t
group by name, thing
) x
where rn = 1
See running example at db fiddle.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论