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
    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.


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?

1 Answer 1


Use row_number

select id
from (  
     select u.id,
            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';


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


Written differently

select u.id
from  user u 
inner join (  select  user_id,
                      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';
  • Yay, thank you, it works as expected!
    – trogwar
    May 11 at 8:40
  • Any suggestions on optimizing this?
    – trogwar
    May 11 at 8:40
  • My user table has 300k records and payment 1m+. This query takes 15-20s on macbook pro.
    – trogwar
    May 11 at 8:41
  • @trogwar please post table description and execution plan, even though I think you should open another thread for performance improvement. May 11 at 8:51
  • Got it thank you
    – trogwar
    May 11 at 8:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.