How to get only last relation sorted by datetime

Question

I have users and their payments (ok/fail) and need to select all users that have last payment failed (by date). Mariadb 10+.

That's schema and how i've tried to solve this, but definitely wrong: http://sqlfiddle.com/#!9/99e867d/6/0

Result should be only user id 20 because user 10 has last payment OK. And last payment of user 10 should be OK from 2020-04-04, not from 2020-03-03 as for now.

---

Thanks for the answer, i wrote query like this and it works as expected now:

SELECT t.user_id
FROM (
    SELECT p.user_id AS user_id
        , p.status   AS last_status
        , ROW_NUMBER() OVER( PARTITION BY p.user_id ORDER BY p.created DESC ) AS rownum
    FROM `payment` AS p
) AS t
WHERE t.rownum = 1
AND t.last_status = 'FAIL';

But execution times are started to be bad (up to 5-6 seconds) when i apply this to tables with 1m+ records.

EXPLAIN EXTENDED:

id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    1206883 100.00  Using where; Using filesort
2   DERIVED p   ALL NULL    NULL    NULL    NULL    1206883 100.00  Using where; Using temporary

I already have index on payment(user_id), tried to add index payment(user_id,created) but no luck – execution plan is the same.

Any suggestions how to improve this?

Answer 1

Use row_number

select id
from (  
     select u.id,
            p.status,
            row_number()over(partition by user_id order by created desc )rn    
     from user u
     inner join payment p on u.id=p.user_id
     ) tbl 
where rn=1 and status='FAIL';

Example

partition by user_id order by created desc assign per each user_id an incrtementing number starting from latest created date.

Filter in the outer query , latest date and status equal to FAIL

---

Edit

Written differently

select u.id
from  user u 
inner join (  select  user_id,
                      status,
                      row_number()over(partition by user_id order by created desc )rn    
              from payment 
           ) p on u.id=p.user_id
where p.rn=1 and p.status='FAIL';