Best 28 result into the sum

Question

I have the following QUERY and I need only the sum of the best 28 results

SELECT id_person, sum(points) 
FROM ranking 
WHERE id_open>=847 and id_club=2 and id_person!='91' 
GROUP BY id_person
ORDER BY sum(points) desc, id_ranking

Each player (id_person), in this serie (id_open=847 or more), in this club (id_club=2) can play about 56 games, but only 28 best result counts for ranking, in other words, I'll despise 28 worst results.

** EDITED QUERY ** (id_ranking isn't necessary in ORDER BY)

SELECT id_person, sum(points) 
FROM ranking 
WHERE id_open>=847 and id_club=2 and id_person!='91' 
GROUP BY id_person
ORDER BY sum(points) desc

Answer 1

If I am understanding correctly...

Your goal: Display players (i.e. id_person) in DESC order based on how many points that person makes in his/her best 28 games.

Here is a query that potentially does that:

SELECT id_person, sum(points) as s
FROM (
SELECT id_person,
       points,
       @rownum := if(@person = id_person, @rownum + 1, 1) as pointRank,
       @person := id_person as dummy
FROM (SELECT id_person, points
      FROM ranking
      WHERE id_open >= 847 and id_club = 2 and id_person != '91'
      ORDER BY id_person, points DESC
     ) as q1,
     (SELECT @rownum := 0) as r,
     (SELECT @person := '') as s
) as q2
WHERE pointRank <= 28
GROUP BY id_person
ORDER BY s DESC;

SQL Fiddle (Note: leaves out q1's WHERE clause for convenience)

Subquery q1 explanation:

1. Filters out rows based on id_open, id_club, and id_person.
2. Then, orders based on id_person (implicitly ASC) and points (explicitly DESC).
3. Finally, selects the id_person and points fields.

Note: MySQL Group By's are a little special - aggregates will work, but if selecting non-aggregates, a random row will be selected for the group by, not all rows (MYSQL-Group-By-returns-only-first-row)

Subquery q2 explanation:

1. Adds in rownum and person variables to keep track of ranking of game. (generate-column-containing-record-index-in-table)
2. Needs to use the (SELECT @rownum := 0) as r and (SELECT @person := '') as s to set the variables. (These variables could be set outside too.)
3. if(@person := id_person, @rownum + 1, 1) is necessary to reset the ranking of games per person.

Note: Important to initialize the person variable to an empty string, as opposed to 0. Not too sure why, but if you do not, then it will not reset the rownum variable correctly.

Overall query explanation:

1. All row numbers (i.e. pointRank) less than or equal to 28 (basically, the 28 best point scores per id_person) are kept.
2. After this filtering, each id_person will be grouped again.
3. Finally, the top 28 games' points will be summed up and labeled as s, and these pairs (id_person,s) will be ordered from highest to lowest.

Here is another StackOverflow question with a good article link inside (highly recommended read): (limit-within-group)

Note: I cannot guarantee this will work though, since I do not know what your table looks like. That information would help.