SQL get data per candidate with MAX priority

Question

I have this table lead

id,candidate_id,job_id,stage,created_at,updated_at
1,2,1,3,"2023-09-04 11:35:18","2023-09-04 11:53:12"
2,2,2,2,"2023-09-04 11:36:28","2023-09-05 02:13:13"
3,3,2,1,"2023-09-04 11:36:35","2023-09-04 11:36:35"
4,3,3,2,"2023-09-04 11:59:41","2023-09-05 00:52:29"
5,4,3,1,"2023-09-04 23:58:49","2023-09-04 23:58:49"
6,4,1,1,"2023-09-05 01:07:09","2023-09-05 01:07:09"

and I was able to get 1 lead per candidate with max updated_at

SELECT a.*
FROM lead a
LEFT JOIN lead b ON a.candidate_id = b.candidate_id AND b.updated_at > 
a.updated_at
WHERE b.id IS NULL 

here's the result

id,candidate_id,job_id,stage,created_at,updated_at
2,2,2,2,"2023-09-04 11:36:28","2023-09-05 02:13:13"
4,3,3,2,"2023-09-04 11:59:41","2023-09-05 00:52:29"
6,4,1,1,"2023-09-05 01:07:09","2023-09-05 01:07:09"

But I also want to get lead with high priority, so I should get ids 1, 4, and 6

Answer 1

try a query like this:

SELECT *
FROM `lead` where (candidate_id,updated_at) IN (
SELECT MAX(candidate_id), MAX(updated_at)
FROM `lead` GROUP BY candidate_id );

sample

mysql> SELECT * FROM `lead`;
+----+--------------+--------+-------+---------------------+---------------------+
| id | candidate_id | job_id | stage | created_at          | updated_at          |
+----+--------------+--------+-------+---------------------+---------------------+
|  1 |            2 |      1 |     3 | 2023-09-04 11:35:18 | 2023-09-04 11:53:12 |
|  2 |            2 |      2 |     2 | 2023-09-04 11:36:28 | 2023-09-05 02:13:13 |
|  3 |            3 |      2 |     1 | 2023-09-04 11:36:35 | 2023-09-04 11:36:35 |
|  4 |            3 |      3 |     2 | 2023-09-04 11:59:41 | 2023-09-05 00:52:29 |
|  5 |            4 |      3 |     1 | 2023-09-04 23:58:49 | 2023-09-04 23:58:49 |
|  6 |            4 |      1 |     1 | 2023-09-05 01:07:09 | 2023-09-05 01:07:09 |
+----+--------------+--------+-------+---------------------+---------------------+
6 rows in set (0.00 sec)

mysql> SELECT *
    -> FROM `lead` where (candidate_id,updated_at) IN (
    -> SELECT MAX(candidate_id), MAX(updated_at)
    -> FROM `lead` GROUP BY candidate_id );
+----+--------------+--------+-------+---------------------+---------------------+
| id | candidate_id | job_id | stage | created_at          | updated_at          |
+----+--------------+--------+-------+---------------------+---------------------+
|  2 |            2 |      2 |     2 | 2023-09-04 11:36:28 | 2023-09-05 02:13:13 |
|  4 |            3 |      3 |     2 | 2023-09-04 11:59:41 | 2023-09-05 00:52:29 |
|  6 |            4 |      1 |     1 | 2023-09-05 01:07:09 | 2023-09-05 01:07:09 |
+----+--------------+--------+-------+---------------------+---------------------+
3 rows in set (0.01 sec)

mysql>