In MySQL, how can I find the sum of the N largest values grouped on a particular column?

Question

Possible Duplicate:
mysql: Using LIMIT within GROUP BY to get N results per group?

I have the following data.

id      val
smith   20
smith   10
smith    8
smith   30
jones   40
jones   10
jones   30
jones   30

What I want then is to group by id and sum the two largest values for each id.

smith   50  from 30+20
jones   70  from 40+30

Thanks.

score 2 · Answer 1 · edited May 23 '17 at 11:43

2

If you weren't worried about getting the sum of only the top two values for each ID, you would want this:

SELECT SUM(val) FROM table_name GROUP BY id ORDER BY id ASC

However, the "How do I get the top N values within a group" question is a duplicate, answered here: Using LIMIT within GROUP BY to get N results per group?

edited May 23 '17 at 11:43

Community

answered Nov 01 '12 at 18:19

David Grenier

1 Answers1