mysql - Find the latest rows by filtering the status

Question

I have a table called person_list. The data is,

Insert into person_list(person_allocation_id, person_id, created_datetime, boss_user_name, allocation_status_id) values
(111008, 1190016, '2021-01-05 11:09:25', 'Rajesh', '2'),
(111007, 1190015, '2020-12-12 09:23:31', 'Sushmita', '2'),
(111006, 1190014, '2020-12-11 10:48:26', '', '3'),
(111005, 1190014, '2020-12-10 13:46:15', 'Rangarao', '2'),
(111004, 1190014, '2020-12-10 13:36:10', '', '3');

Here person_allocation_id is the primary key.

person_id may be duplicated some times.

All of these rows are sorted by person_allocation_id (in descending order)

Now, I would like to filter the rows which are having allocation_status_id = '2' and boss_user_name should be non-empty for the person_id.

The difficulty here is that I have to exclude the row if the person_id is having allocation_status_id = '3' as their latest status (according to date).

I am unable to understand how could I compare the dates in one row with another in the previous row.

So finally I should get only 2 rows in my final result set (person_allocation_id are 111008 and 111007).

Somehow I achieved this in Oracle.

select person_id, person_allocation_id, create_datetime, boss_user_name, allocation_status_id 
from (
select person_id, person_allocation_id, create_datetime, boss_user_name, allocation_status_id, 
       rank() over (partition by person_id order by create_datetime desc) rnk
from person_list 
where allocation_status_id = '2') 
where rnk = 1;

But, I need this for MySql DB. Anyone, please help?

Thanks.

question from:https://stackoverflow.com/questions/65680129/find-the-latest-rows-by-filtering-the-status

Answer 1

SELECT t1.*
FROM person_list t1
JOIN ( SELECT MAX(t2.person_allocation_id) person_allocation_id, t2.person_id
       FROM person_list t2
       GROUP BY t2.person_id ) t3 USING (person_allocation_id, person_id)
WHERE t1.allocation_status_id = '2'

fiddle

Add more conditions to WHERE clause if needed (for example, AND boss_user_name != '').