0

I want to achieve a function in MySQL like 

select * from (
    select *, row_number() over (partition by uid order by last_login desc) as rn 
    from test) 
where rn = 1

Then I found a magical SQL as follows:

select * from test as a 
where typeindex = (select max(b.last_login) 
                   from test as b 
                   where a.uid = b.uid);

It can fit my requirement and efficiency is good but it's a bit difficult for me to understand the procedure of this query particularly the where a.uid = b.uid in the sub-query.

Could any body give a brief flow of how this query works? Thanks for your help in advance.

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
user2894829
  • 775
  • 1
  • 6
  • 26
  • 1
    It is a Correlated Subquery. Refer documentation: https://dev.mysql.com/doc/refman/5.5/en/correlated-subqueries.html – Madhur Bhaiya Aug 29 '19 at 08:14
  • 1
    Also, the "magical SQL" query has much better/performant alternatives available. Check this answer, for instance: https://stackoverflow.com/a/9192763/2469308 – Madhur Bhaiya Aug 29 '19 at 08:16
  • The magical query would be my preferred solution. You should check explain to see which is more efficient. – P.Salmon Aug 29 '19 at 09:16

0 Answers0