MariaDB 10.4

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `category_contact` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `contact_id` bigint(20) UNSIGNED NOT NULL,
  `category_id` tinyint(3) UNSIGNED NOT NULL,
  `total_points` bigint(20) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `category_contact` (`id`, `contact_id`, `category_id`, `total_points`) VALUES
(1, 0, 4, 17),
(2, 0, 5, 21),
(3, 2, 2, 24),
(4, 3, 1, 12),
(5, 3, 5, 9),
(6, 5, 1, 5),
(7, 5, 2, 11),
(8, 5, 5, 20),
(9, 6, 0, 24),
(10, 6, 3, 15),
(11, 6, 5, 6),
(12, 7, 1, 22),
(13, 7, 4, 15),
(14, 8, 0, 11),
(15, 9, 2, 15),
(16, 10, 0, 16),
(17, 10, 1, 5),
(18, 10, 5, 12),
(19, 11, 0, 16),
(20, 11, 2, 18),
(21, 11, 3, 15),
(22, 11, 4, 4),
(23, 12, 4, 20),
(24, 12, 5, 21),
(25, 13, 1, 20),
(26, 13, 3, 9),
(27, 13, 4, 11),
(28, 14, 1, 4),
(29, 14, 2, 7),
(30, 14, 3, 9),
(31, 15, 3, 22),
(32, 16, 5, 6),
(33, 17, 0, 6),
(34, 17, 4, 6),
(35, 18, 2, 15),
(36, 18, 2, 22),
(37, 18, 3, 5),
(38, 18, 4, 3),
(39, 19, 0, 11),
(40, 19, 1, 8);
SET @needed_category_id := 4;
WITH 
cte AS (
    SELECT id, contact_id, category_id, total_points,
           ROW_NUMBER() OVER (PARTITION BY contact_id
                              ORDER BY total_points DESC, category_id = @needed_category_id DESC) rn
    FROM category_contact
)
SELECT id, contact_id, category_id, total_points, category_id = @needed_category_id needed_category
FROM cte
WHERE rn = 1;


id
contact_id
category_id
total_points
needed_category


2
0
5
21
0

3
2
2
24
0

4
3
1
12
0

8
5
5
20
0

9
6
0
24
0

12
7
1
22
0

14
8
0
11
0

15
9
2
15
0

16
10
0
16
0

20
11
2
18
0

24
12
5
21
0

25
13
1
20
0

30
14
3
9
0

31
15
3
22
0

32
16
5
6
0

34
17
4
6
1

36
18
2
22
0

39
19
0
11
0

id	contact_id	category_id	total_points	needed_category
2	0	5	21	0
3	2	2	24	0
4	3	1	12	0
8	5	5	20	0
9	6	0	24	0
12	7	1	22	0
14	8	0	11	0
15	9	2	15	0
16	10	0	16	0
20	11	2	18	0
24	12	5	21	0
25	13	1	20	0
30	14	3	9	0
31	15	3	22	0
32	16	5	6	0
34	17	4	6	1
36	18	2	22	0
39	19	0	11	0

id	contact_id	category_id	total_points	needed_category
2	0	5	21	0
3	2	2	24	0
4	3	1	12	0
8	5	5	20	0
9	6	0	24	0
12	7	1	22	0
14	8	0	11	0
15	9	2	15	0
16	10	0	16	0
20	11	2	18	0
24	12	5	21	0
25	13	1	20	0
30	14	3	9	0
31	15	3	22	0
32	16	5	6	0
34	17	4	6	1
36	18	2	22	0
39	19	0	11	0

id	contact_id	category_id	total_points	needed_category
2	0	5	21	0
3	2	2	24	0
4	3	1	12	0
8	5	5	20	0
9	6	0	24	0
12	7	1	22	0
14	8	0	11	0
15	9	2	15	0
16	10	0	16	0
20	11	2	18	0
24	12	5	21	0
25	13	1	20	0
30	14	3	9	0
31	15	3	22	0
32	16	5	6	0
34	17	4	6	1
36	18	2	22	0
39	19	0	11	0