1

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.

  • Which version of mysql? – ypercubeᵀᴹ Jun 30 '17 at 9:30
  • @ypercubeᵀᴹ, 5.7.18 – orangetime Jun 30 '17 at 9:36
2

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

  • I need all the records from user table, from your solution users without records in login_track is not listing. – orangetime Jun 30 '17 at 9:09
  • @orangetime Could you add the desired result to the question, actually it only shows user_id and login-date. – McNets Jun 30 '17 at 9:10
  • @Mc_nets, updated desired result to the question. – orangetime Jun 30 '17 at 9:15
  • Do you need all users or only those that have login date? – McNets Jun 30 '17 at 9:21
  • 1
    all users with or without login_date. If they have login_date then recent two login dates per user. If no login date then NULL value. – orangetime Jun 30 '17 at 9:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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