By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE person (
id INT NOT NULL PRIMARY KEY,
firstname VARCHAR(10),
gender VARCHAR(1),
age INT
);
INSERT INTO person (id, firstname, gender, age) VALUES
(1, 'Adams', 'M', 33),
(2, 'Matt', 'M', 31),
(3, 'Grace', 'F', 25),
(4, 'Harry', 'M', 20),
(5, 'Scott', 'M', 30),
(6, 'Sarah', 'F', 30),
(7, 'Tony', 'M', 30),
(8, 'Lucy', 'F', 27),
(9, 'Zoe', 'F', 30),
(10, 'Megan', 'F', 26),
(11, 'Emily', 'F', 20),
(12, 'Peter', 'M', 20),
(13, 'John', 'M', 21),
(14, 'Kate', 'F', 35),
(15, 'James', 'M', 32),
(16, 'Cole', 'M', 25),
(17, 'Dennis', 'M', 27),
(18, 'Smith', 'M', 35),
(19, 'Zack', 'M', 35),
(20, 'Jill', 'F', 25);
Records: 20 Duplicates: 0 Warnings: 0
SELECT person.*, @rank := CASE
WHEN @partval = gender AND @rankval = age THEN @rank
WHEN @partval = gender AND (@rankval := age) IS NOT NULL THEN @rank + 1
WHEN (@partval := gender) IS NOT NULL AND (@rankval := age) IS NOT NULL THEN 1
END AS rnk
FROM person, (SELECT @rank := NULL, @partval := NULL, @rankval := NULL) AS x
ORDER BY gender, age;
| id | firstname | gender | age | rnk |
|---|---|---|---|---|
| 11 | Emily | F | 20 | 1 |
| 20 | Jill | F | 25 | 2 |
| 3 | Grace | F | 25 | 2 |
| 10 | Megan | F | 26 | 3 |
| 8 | Lucy | F | 27 | 4 |
| 6 | Sarah | F | 30 | 5 |
| 9 | Zoe | F | 30 | 5 |
| 14 | Kate | F | 35 | 6 |
| 4 | Harry | M | 20 | 1 |
| 12 | Peter | M | 20 | 1 |
| 13 | John | M | 21 | 2 |
| 16 | Cole | M | 25 | 3 |
| 17 | Dennis | M | 27 | 4 |
| 7 | Tony | M | 30 | 5 |
| 5 | Scott | M | 30 | 5 |
| 2 | Matt | M | 31 | 6 |
| 15 | James | M | 32 | 7 |
| 1 | Adams | M | 33 | 8 |
| 18 | Smith | M | 35 | 9 |
| 19 | Zack | M | 35 | 9 |
SELECT person.*, @rank := CASE
WHEN @partval = gender THEN @rank + 1
WHEN (@partval := gender) IS NOT NULL THEN 1
END AS rnum
FROM person, (SELECT @rank := NULL, @partval := NULL) AS x
ORDER BY gender, age;
| id | firstname | gender | age | rnum |
|---|---|---|---|---|
| 11 | Emily | F | 20 | 1 |
| 20 | Jill | F | 25 | 2 |
| 3 | Grace | F | 25 | 3 |
| 10 | Megan | F | 26 | 4 |
| 8 | Lucy | F | 27 | 5 |
| 6 | Sarah | F | 30 | 6 |
| 9 | Zoe | F | 30 | 7 |
| 14 | Kate | F | 35 | 8 |
| 4 | Harry | M | 20 | 1 |
| 12 | Peter | M | 20 | 2 |
| 13 | John | M | 21 | 3 |
| 16 | Cole | M | 25 | 4 |
| 17 | Dennis | M | 27 | 5 |
| 7 | Tony | M | 30 | 6 |
| 5 | Scott | M | 30 | 7 |
| 2 | Matt | M | 31 | 8 |
| 15 | James | M | 32 | 9 |
| 1 | Adams | M | 33 | 10 |
| 18 | Smith | M | 35 | 11 |
| 19 | Zack | M | 35 | 12 |