MYSQL sum of max score taken users list

user_3895389 0
Viewed 28

I have following table

 CREATE TABLE Table1
    (`userid` varchar(11), `score` int, `type` varchar(22));
    
INSERT INTO Table1
    (`userid`, `score`,`type`)
VALUES
    (11, 2,'leader'),
    (11, 6,'leader'),
    (13, 6,'leader'),
    (15, 4,'leader'),
    (15, 4,'leader'),
    (12, 1,'leader'),
    (14, 1,'leader');

I need to get userid of the maximum score take user. if the max score is the same for two or more user need to get that userid also.

I have try following query

SELECT userid, sum(score) as totalScore 
FROM Table1 WHERE type = "leader" GROUP BY userid 
ORDER BY totalScore DESC;

But it gets all user data, cant get the max score take the first two users id.

enter image description here

But I need to get only first two row of data ..

Please help me

mysql
2 Answers

On MySQL 8+, I suggest using the RANK() analytic function:

WITH cte AS (
    SELECT userid, SUM(score) AS totalScore,
           RANK() OVER (ORDER BY SUM(score) DESC) rnk
    FROM Table1
    WHERE type = 'leader'
    GROUP BY userid
)

SELECT userid, totalScore
FROM cte
WHERE rnk = 1;
user_18632290

if you need just top 2 records add limit in your query :

SELECT userid, sum(score) as totalScore 
FROM Table1 WHERE type = "leader" GROUP BY userid 
ORDER BY totalScore DESC LIMIT 2;
user_142290270
Related