0

i am loosing it over the following problem:

i have a table with participants and points. each participant can have up to 11 point entries of which i only want the sum of the top 6.

in this example lets say we want the top 2 of 3

+----+---------------+--------+
| id | participantid | points |
+----+---------------+--------+
|  1 |   1           |  11    |
+----+---------------+--------+
|  2 |   3           |   1    |
+----+---------------+--------+
|  3 |   3           |   4    |
+----+---------------+--------+
|  4 |   2           |   3    |
+----+---------------+--------+
|  5 |   1           |   5    |
+----+---------------+--------+
|  6 |   2           |  10    |
+----+---------------+--------+
|  7 |   2           |   9    |
+----+---------------+--------+
|  8 |   1           |   3    |
+----+---------------+--------+
|  9 |   3           |   4    |
+----+---------------+--------+

as a result i want something like 

+---------------+--------+
| participantid | points |
+---------------+--------+
|   2           |  19    |
+---------------+--------+
|   1           |  16    |
+---------------+--------+
|   3           |   8    |
+---------------+--------+

(it should be ordered DESC by the resulting points)

is this at all possible with mysql? in one query?

oh and the resulting participant ids should be resolved into the real names from another 'partcipant' table where 

+----+------+
| id | name |
+----+------+
| 1  | what |
+----+------+
| 2  | ev   |
+----+------+
| 3  | er   |
+----+------+

but that should be doable with a join at some point... i know...

zwadl
  • 34
  • 8

1 Answers1

2

Using one of the answers from ROW_NUMBER() in MySQL for row counts, and then modifying to get the top.

SELECT ParticipantId, SUM(Points)
FROM
(
  SELECT a.participantid, a.points, a.id, count(*) as row_number
  FROM scores a
    JOIN scores b ON a.participantid = b.participantid AND cast(concat(a.points,'.', a.id) as decimal) <= cast(concat(b.points,'.', b.id) as decimal)
   GROUP BY a.participantid, a.points, a.id
 ) C
 WHERE row_number IN (1,2)
 GROUP BY ParticipantId

Had an issue with ties until I arbitrarily broke them with the id

Community
  • 1
  • 1
Stephen S.
  • 1,616
  • 16
  • 14
  • could you give me an example? maybe on the above example tables? – zwadl May 27 '15 at 19:48
  • Hmm, re-reading you want the top X per participant, that's a little uglier than I initially read and has me wishing for https://msdn.microsoft.com/en-ca/library/ms186734.aspx so I could partition. But we can fudge it to fit with mysql. I ended up using one of the answers from http://stackoverflow.com/questions/1895110/row-number-in-mysql instead... er editing the answer because I can't paste enough – Stephen S. May 27 '15 at 21:24
  • impressive! thank you very much! this works perfectly! – zwadl May 29 '15 at 18:39
  • as i want to also want to get better at this and learn this stuff would you please enlighten me by explaining step by step how you build this query? – zwadl May 29 '15 at 18:46
  • ok... doesn't seem to work after all... if i apply this to my database and add 10 rows for one participant it won't work. applying the subselect the row number results are 7 times 7 and 3 times 10... the points for this participant are 7 times 5 and 3 times 0... so i guess thats where the problem lies – zwadl May 30 '15 at 11:18
  • did it! i do understand it now AND found the solution: before casting to decimal one has to multiply the concat result by lets say 10000 (or at least the number of entries in the table) then the row count will be correct! – zwadl May 30 '15 at 12:26