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() OVER (PARTITION BY name ORDER BY name, position) AS `rank`
FROM drivers LEFT JOIN results ON drivers.id = results.driver_id
) 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 |