add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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