MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t (
    id   VARCHAR(10),
    rate DECIMAL(10, 1),
    year INT
);
INSERT INTO t (id, rate, year) VALUES
('p01',  8.0, 2006),
('p01',  7.4, 2003),
('p01',  6.8, 2008),
('p01',  5.9, 2001),
('p01',  5.3, 2007),
('p01',  4.4, 2009),
('p01',  3.9, 2002),
('p01',  3.5, 2004),
('p01',  2.1, 2005),
('p01',  0.8, 2000),
('p02', 12.5, 2001),
('p02', 12.4, 2004),
('p02', 12.2, 2002),
('p02', 10.3, 2003),
('p02',  8.7, 2000),
('p02',  4.6, 2006),
('p02',  3.3, 2007);
SELECT t.id, t.rate, t.year, COUNT(l.rate) AS rank
FROM t
LEFT JOIN t AS l ON t.id = l.id AND t.rate < l.rate
GROUP BY t.id, t.rate, t.year
HAVING COUNT(l.rate) < 5
ORDER BY t.id, t.rate DESC, t.year


id
rate
year
rank


p01
8.0
2006
0

p01
7.4
2003
1

p01
6.8
2008
2

p01
5.9
2001
3

p01
5.3
2007
4

p02
12.5
2001
0

p02
12.4
2004
1

p02
12.2
2002
2

p02
10.3
2003
3

p02
8.7
2000
4

id	rate	year	rank
p01	8.0	2006	0
p01	7.4	2003	1
p01	6.8	2008	2
p01	5.9	2001	3
p01	5.3	2007	4
p02	12.5	2001	0
p02	12.4	2004	1
p02	12.2	2002	2
p02	10.3	2003	3
p02	8.7	2000	4