sql SELECT count all points from different entries limit by 10 per ID

Question

i have a table that looks like this:

  ID    GameID  DateID  Points  Place
-------------------------------------
  10      1       1      100      1 
  11      1       1       90      2 
  12      1       1       80      3 
  13      1       1       70      4 
  14      1       1       60      5 
  10      1       1      100      1 
  10      1       1       50      1 
  10      1       1      100      1 
  10      1       1      100      1
  10      1       1      100      1
  10      1       1      100      1
  10      1       1      100      1
  10      1       1      100      1
  10      1       1      100      1
  10      1       1       50      5
  10      1       1       50      5
  12      1       1      100      1
-------------------------------------

I want a table with two columns, one for the total points (summated scores/points) of one player and one for the id of the player. But for one player only ten scores may be counted, so for example if one player played thirteen times, only the ten highest scores are counted. For the example above I want a table that looks like this:

  ID    totalPoints
-------------------
  10      950         
  11      90          
  12      180         
  13      70          
  14      60              
------------------

At the moment I tried this:

SELECT ID, 
       sum(Points) AS totalPoints
    FROM (SELECT Points, ID
              FROM Gamer
              ORDER BY Points DESC LIMIT 10) AS totalPoints
    ORDER BY Points DESC

but it limits the entries at all to ten and not to ten per player.

I hope anybody can help me :)

If none of the options in the duplicate work for you, and you truly get stuck, then drop a message here and I think I can ressurect the question. — Tim Biegeleisen, Nov 30 '16 at 14:58
Yes i tried a couple of them. But unfortunately i can't get it to work :/ — egolive, Nov 30 '16 at 16:53

score 0 · Answer 1 · answered Nov 30 '16 at 22:29

In all existing versions:

DELIMITER $
CREATE FUNCTION `totalPoints`(gamer_id INT) RETURNS int(11)
BEGIN
DECLARE s INT DEFAULT 0;
SELECT SUM(Points) INTO s FROM ( SELECT Points FROM Gamer WHERE ID=gamer_id ORDER BY Points DESC LIMIT 10) sq;
RETURN s;
END$
DELIMITER ;

SELECT DISTINCT ID, totalPoints(ID) FROM Gamer;

Alternative in MariaDB 10.2 (currently Beta), which has window functions:

SELECT ID, SUM(Points) FROM ( 
    SELECT ID, Points, ROW_NUMBER() 
    OVER (PARTITION BY ID ORDER BY Points DESC) AS nm 
    FROM Gamer
) sq WHERE nm <= 10 GROUP BY ID;

I'm pretty sure there are other ways to do the same, these two are first that came to mind.

sql SELECT count all points from different entries limit by 10 per ID

1 Answers1