By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE drivers
(`id` int, `name` varchar(11)
);
INSERT INTO drivers (`id`, `name`) VALUES
(1, 'Driver A'),
(2, 'Driver B');
CREATE TABLE results (
`driver_id` int,
`position` int
);
INSERT INTO results (`driver_id`, `position`) VALUES
(1, 2),
(1, 1),
(1, 7),
(1, 8),
(2, 2),
(2, 4),
(2, 9),
(2, 1);
SELECT name, `rank`, position FROM (
SELECT name, position,
@rank:= IF(@prev = name, @rank + 1, 1) AS `rank`,
@prev:= name
FROM (
SELECT *
FROM drivers LEFT JOIN results ON drivers.id = results.driver_id
JOIN (SELECT @rank := 1) AS init_rank
JOIN (SELECT @prev := '') AS init_prev
ORDER BY name, results.position ASC
) AS temp
) AS derived
WHERE `rank` <= 3
ORDER BY name, `rank`
| name | rank | position |
|---|---|---|
| Driver A | 1 | 1 |
| Driver A | 2 | 2 |
| Driver A | 3 | 7 |
| Driver B | 1 | 1 |
| Driver B | 2 | 2 |
| Driver B | 3 | 4 |