MySQL 8.0

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

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