从SQL中选择最大值从组中。

huangapple
go评论95阅读模式
英文:

Select max from groups in SQL

问题

  1. SELECT 
  2.     NAME, 
  3.     THING, 
  4.     MAX(SUM(NUM)) 
  5. FROM this_table 
  6. GROUP BY NAME, THING;
英文:

I have a table similar to the following:

  1. NAME,THING,NUM
  2. abc,thing1,9
  3. abc,thing1,1
  4. abc,thing2,3
  5. abc,thing2,1
  6. jkl,thing1,88
  7. jkl,thing1,12
  8. jkl,thing2,13
  9. jkl,thing2,14
  10. xyz,thing1,37
  11. xyz,thing1,3
  12. xyz,thing2,101
  13. 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:

  1. abc,thing1,10
  2. jkl,thing1,100
  3. xyz,thing2,200

This query:

  1. SELECT 
  2.     NAME, 
  3.     THING, 
  4.     SUM(NUM) 
  5. FROM this_table 
  6. GROUP BY NAME, THING;

yields this result:

  1. abc,thing1,10
  2. abc,thing2,4
  3. jkl,thing1,100
  4. jkl,thing2,27
  5. xyz,thing1,40
  6. xyz,thing2,200

but I just want the highest value for each name and thing.

答案1

得分: 3

你没有提到数据库类型，所以我会假设是 PostgreSQL。你可以执行以下操作：

  1. select *
  2. from (
  3.   select name, thing, sum(num) as s,
  4.     row_number() over(partition by name order by sum(num) desc) as rn
  5.   from t
  6.   group by name, thing
  7. ) x
  8. where rn = 1

在 db fiddle 上查看运行示例。

英文:

You don't mention the database, so I'll assume it's PostgreSQL. You can do:

  1. select *
  2. from (
  3.   select name, thing, sum(num) as s,
  4.     row_number() over(partition by name order by sum(num) desc) as rn
  5.   from t
  6.   group by name, thing
  7. ) x
  8. where rn = 1

See running example at db fiddle.

huangapple
  • 本文由 发表于 2023年7月13日 23:56:51
  • 转载请务必保留本文链接：https://go.coder-hub.com/76681317.html
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定