Mysql Select recent 2 records for each group[user]

greatest-n-per-groupgroup-concatenationjoin;MySQLmysql-5.7

I have two tables users and login_track. Each user may or may not have login records in login_track table. I want to retrieve recent 2 login dates for each user.

Table structures as below:
users: 

user_id    name
     1     John
     2     Mike
     3     Anderson
    .....

login_track: 

  track_id   user_id   login_date
   100           1    2017-06-20
   101           1    2017-06-21
   102           2    2017-06-21
   103           1    2017-06-23
   104           2    2017-06-23
   105           1    2017-06-27

So desired result should be. 

 user_id  name         login_date
    1      John        2017-06-27, 2017-06-23
    2      Mike        2017-06-23, 2017-06-21
    3      Anderson    NULL

Note : User id 3 (Anderson) has no records in login_track table, even though it should list in the final result with NULL value as login_date column.

Best Answer

In this case I'd suggest to use a subquery to count rows using a variable.

select   user_id, name, group_concat(login_date)
from     (select user_id, login_date, name
          from (select user_id, login_date, name,
                       if(@last_user = user_id, @rn := @rn + 1, @rn := 0) rn,
                       @last_user := user_id 
                from (select @last_user := 0, @rn := 0) a,
                     (select users.user_id, login_date, users.name
                      from users
                      left join login_track
                      on users.user_id = login_track.user_id
                      order by users.user_id, login_date desc) b
               ) c
          where rn <= 1
         ) d
group by user_id;


| user_id | name     | group_concat(login_date) |
|---------|----------|--------------------------|
| 1       | John     | 2017-06-27,2017-06-23    |
| 2       | Mike     | 2017-06-23,2017-06-21    |
| 3       | Anderson | NULL                     |

rextester here

Related Question