Re: SUM problem.

[Peter Brawley]

Patric,

>sort the 3 users based on their 2 best results ?

If you read "Within-group quotas (Top N per group)" at http://www.artfulsoftware.com/infotree/queries.php, you see that the 2nd query in that section ...

SELECT t1.id, t1.entrydate, count(*) AS earlier
FROM test AS t1
JOIN test AS t2 ON t1.id=t2.id AND t1.entrydate >= t2.entrydate
GROUP BY t1.id, t1.entrydate
HAVING earlier <= 2;

which finds the two earliest entries per ID for that example dataset, transposes to the following query for your dataset to find the two highest `poang` values per `deltagare`:

SELECT a.deltagare, a.poang, count(*) AS higher
FROM lepok_resultat a
JOIN lepok_resultat b ON a.deltagare=b.deltagare AND a.poang <= b.poang
GROUP BY a.deltagare, a.poang
HAVING higher <= 2
ORDER BY a.deltagare,higher ;
+-----------+-------+--------+
| deltagare | poang | higher |
+-----------+-------+--------+
| 1         |    12 |      1 |
| 1         |    10 |      2 |
| 2         |    18 |      1 |
| 2         |     7 |      2 |
+-----------+-------+--------+

The logic is a two-step:
1. Use a join to rank by higher score per user
2. Restrict output to rank <=2.

Now, as that article makes plain, this query has scaling problems, so you might not want to run it on large tables, and it does not bring in data from your other tables, but perhaps we should pause before considering those aspects, to ensure that the above result is what you expect, and that you understand how to adjust it to finding the top 8 or whatever quota you want.

PB
http://www.artfulsoftware.com



Edited 1 time(s). Last edit at 03/16/2009 04:17PM by Peter Brawley.