rraayy - 1 year ago 73
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);
``````

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download