Category: MySQL FAQ

Trying to sum the top 5 entries in each group.

SELECT
    theId,
    SUM(theData) FROM (
        (SELECT
            @count := 0, 
            @oldId := 0 AS theId, 
            0 AS theData) 
        UNION 
        (SELECT 
            @count := if(@oldId = id, @count+1, 0), 
            @oldId := id, 
            if(@count < 5, a, 0) 
        FROM 
            (SELECT * FROM theTable ORDER BY id, a DESC) AS theTable 
        ) 
    ) AS theView 
WHERE theId != 0 
GROUP BY theId;

Table population:

INSERT INTO `thetable` VALUES 
    (1,1), (1,2), (1,3), (1,4),
    (1,5), (1,6), (1,7), (2,1),
    (2,2), (2,3), (2,4), (2,5);