Getting the last 50 rows for each group in group by

Question

I have this query but it is only showing the last 5 rows instead of limiting the amount of rows the group by gets

I only want the last 50 rows for each person to be sum and in the group.

SELECT playerid, SUM(gamesplayed) AS totalgames, SUM(playtimes) AS playtimeTotal, SUM(Kills) AS totalkills 
FROM plugin_game 
WHERE gamesplayed=1 
GROUP BY playerid 
ORDER BY totalkills DESC 
LIMIT 50

playerid  totalgames        playtimeTotal         totalkills    
797749       8                  3076                   678  
53854        8                  5982                   635  
24398        8                  3277                   575  
464657       4                  1325                   387  
65748        4                  3390                   368  
651532       4                  3219                   354  
287378       6                  3893                   350  
753808       4                  2565                   323  
731631       4                  1733                   256  
665338       4                  1971                   255  
569648       2                  2041                   244  
56488        4                  2636                   157  
006985       3                  785                    93   
58640        1                  432                    72

If i change the LIMIT to 5 it only shows

playerid  totalgames        playtimeTotal         totalkills    
797749       8                  3076                   678  
53854        8                  5982                   635  
24398        8                  3277                   575  
464657       4                  1325                   387  
65748        4                  3390                   368

so if we use 5 games as an example, i only want to get the SUM for the past 5 games for the group

Please add some sample data to better explain your question. — Tim Biegeleisen, Mar 17 '22 at 01:41
Does this answer your question? [Using LIMIT within GROUP BY to get N results per group?](https://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group) — Andrea Olivato, Mar 17 '22 at 01:48

Darshit Parmar · Answer 1 · 2022-03-17T05:15:14.580

0

This should work in postgre sql!

SELECT   playerid,
         SUM(gamesplayed) over w AS totalgames,
         SUM(playtimes) over w   AS playtimetotal,
         SUM(kills) over w       AS totalkills,
         ROW_NUMBER() over w AS row 
FROM     plugin_game 
window w AS (PARTITION BY playerid ORDER BY totalkills DESC)
WHERE    gamesplayed=1 and row <=50

edited Mar 17 '22 at 05:15

answered Mar 17 '22 at 05:07

Darshit Parmar

68
7

Getting the last 50 rows for each group in group by

1 Answers1