MySQL 5.5

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

CREATE TABLE t (
    pkid  INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    catid VARCHAR(10),
    value INT
)
INSERT INTO t (catid, value) VALUES
('p01', 100),
('p01',  90),
('p01',  90),
('p01',  80),
('p01',  80),
('p01',  80),
('p01',  70),
('p01',  60),
('p01',  50),
('p01',  40),
('p02', 100)

Records: 11  Duplicates: 0  Warnings: 0


SELECT t.pkid, t.catid, t.value, COUNT(b.value) + 1 AS rank
FROM t
LEFT JOIN t AS b ON b.catid = t.catid AND b.value > t.value
GROUP BY t.pkid, t.catid, t.value
HAVING COUNT(b.value) + 1 <= 5
ORDER BY t.catid, t.value DESC, t.pkid


pkid
catid
value
rank


1
p01
100
1

2
p01
90
2

3
p01
90
2

4
p01
80
4

5
p01
80
4

6
p01
80
4

11
p02
100
1


SELECT t.pkid, t.catid, t.value, COUNT(DISTINCT b.value) + 1 AS dense_rank
FROM t
LEFT JOIN t AS b ON b.catid = t.catid AND b.value > t.value
GROUP BY t.pkid, t.catid, t.value
HAVING COUNT(DISTINCT b.value) + 1 <= 5
ORDER BY t.catid, t.value DESC, t.pkid


pkid
catid
value
dense_rank


1
p01
100
1

2
p01
90
2

3
p01
90
2

4
p01
80
3

5
p01
80
3

6
p01
80
3

7
p01
70
4

8
p01
60
5

11
p02
100
1


SELECT t.pkid, t.catid, t.value, COUNT(b.value) + 1 AS row_number
FROM t
LEFT JOIN t AS b ON b.catid = t.catid AND (b.value > t.value OR b.value = t.value AND b.pkid < t.pkid)
GROUP BY t.pkid, t.catid, t.value
HAVING COUNT(b.value) + 1 <= 5
ORDER BY t.catid, t.value DESC, t.pkid


pkid
catid
value
row_number


1
p01
100
1

2
p01
90
2

3
p01
90
3

4
p01
80
4

5
p01
80
5

11
p02
100
1

pkid	catid	value	dense_rank
1	p01	100	1
2	p01	90	2
3	p01	90	2
4	p01	80	3
5	p01	80	3
6	p01	80	3
7	p01	70	4
8	p01	60	5
11	p02	100	1

pkid	catid	value	dense_rank
1	p01	100	1
2	p01	90	2
3	p01	90	2
4	p01	80	3
5	p01	80	3
6	p01	80	3
7	p01	70	4
8	p01	60	5
11	p02	100	1

pkid	catid	value	dense_rank
1	p01	100	1
2	p01	90	2
3	p01	90	2
4	p01	80	3
5	p01	80	3
6	p01	80	3
7	p01	70	4
8	p01	60	5
11	p02	100	1