MySQL 8.0

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)

Records: 10  Duplicates: 0  Warnings: 0


SELECT *
     , ROW_NUMBER() OVER (PARTITION BY catid ORDER BY value DESC) AS "row_number"
     , RANK() OVER (PARTITION BY catid ORDER BY value DESC) AS "rank"
     , DENSE_RANK() OVER (PARTITION BY catid ORDER BY value DESC) AS "dense_rank"
FROM t
ORDER BY catid, value DESC, pkid


pkid
catid
value
row_number
rank
dense_rank


1
p01
100
1
1
1

2
p01
90
2
2
2

3
p01
90
3
2
2

4
p01
80
4
4
3

5
p01
80
5
4
3

6
p01
80
6
4
3

7
p01
70
7
7
4

8
p01
60
8
8
5

9
p01
50
9
9
6

10
p01
40
10
10
7


SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY catid ORDER BY value DESC) AS n
    FROM t
) AS x
WHERE n <= 5


pkid
catid
value
n


1
p01
100
1

2
p01
90
2

3
p01
90
3

4
p01
80
4

5
p01
80
5


SELECT *
FROM (
    SELECT *, RANK() OVER (PARTITION BY catid ORDER BY value DESC) AS n
    FROM t
) AS x
WHERE n <= 5


pkid
catid
value
n


1
p01
100
1

2
p01
90
2

3
p01
90
2

4
p01
80
4

5
p01
80
4

6
p01
80
4


SELECT *
FROM (
    SELECT *, DENSE_RANK() OVER (PARTITION BY catid ORDER BY value DESC) AS n
    FROM t
) AS x
WHERE n <= 5


pkid
catid
value
n


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

pkid	catid	value	row_number	rank	dense_rank
1	p01	100	1	1	1
2	p01	90	2	2	2
3	p01	90	3	2	2
4	p01	80	4	4	3
5	p01	80	5	4	3
6	p01	80	6	4	3
7	p01	70	7	7	4
8	p01	60	8	8	5
9	p01	50	9	9	6
10	p01	40	10	10	7

pkid	catid	value	row_number	rank	dense_rank
1	p01	100	1	1	1
2	p01	90	2	2	2
3	p01	90	3	2	2
4	p01	80	4	4	3
5	p01	80	5	4	3
6	p01	80	6	4	3
7	p01	70	7	7	4
8	p01	60	8	8	5
9	p01	50	9	9	6
10	p01	40	10	10	7

pkid	catid	value	row_number	rank	dense_rank
1	p01	100	1	1	1
2	p01	90	2	2	2
3	p01	90	3	2	2
4	p01	80	4	4	3
5	p01	80	5	4	3
6	p01	80	6	4	3
7	p01	70	7	7	4
8	p01	60	8	8	5
9	p01	50	9	9	6
10	p01	40	10	10	7