clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1228811 fiddles created (16681 in the last week).

CREATE TABLE t ( id VARCHAR(10), rate DECIMAL(10, 1), year INT );
 hidden batch(es)


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);
 hidden batch(es)


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
 hidden batch(es)