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 *
, 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 |
| 11 | p02 | 100 | 1 | 1 | 1 |
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 |
| 11 | p02 | 100 | 1 |
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 |
| 11 | p02 | 100 | 1 |
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 |
| 11 | p02 | 100 | 1 |