rraayy rraayy - 9 days ago 6
MySQL Question

MySQL get lowest 3 values by user

I have a MySQL database and I want to SUM the lowest 3 Point by a Person.

+--------+--------+
| Person | Points |
+--------+--------+
| 1 | 15 |
| 1 | 10 |
| 1 | 5 |
| 1 | 10 |
| 2 | 5 |
| 2 | 4 |
| 2 | 3 |
| 2 | 2 |
| 3 | 1 |
| 3 | 1 |
| 3 | 1 |
+--------+--------+


The result what I want:

+-------+-----+
| 1 | 25 |
| 2 | 9 |
| 3 | 3 |
+-------+-----+


But I am really lost how to solve this. This is my Query Until now, :

SELECT person, SUM(points) FROM (SELECT SUM(points) FROM table
GROUP BY person ORDER BY points ASC LIMIT 3)


This is my SQL Create Script:

CREATE TABLE `mytable` (
`person` int(11) DEFAULT NULL,
`points` int(11) DEFAULT NULL
) ;

INSERT INTO `mytable` (`person`, `points`) VALUES
(1, 15),
(1, 10),
(1, 5),
(1, 10),
(2, 5),
(2, 4),
(2, 3),
(2, 2),
(3, 1),
(3, 1),
(3, 1);

Answer

Use a row number in the inner query as a helper to limit the rows.Row number and not limit to avoid the duplicate values problem.Not tested but it should work,too lazy to create a fiddle.

SELECT Person,SUM(Points) FROM
(SELECT Person,Points,
CASE Person
         WHEN @person THEN @rank:= @rank+ 1 
         ELSE @rank:= 1
     END AS rank,
     @person:= person 
FROM t, (SELECT @rank:= 0,@person:='') x
ORDER BY Points ASC)y
WHERE y.rank<=3
GROUP BY Persons

FIDDLE

Comments