Mysql Select recent 2 records for each group[user]

Question

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 structure is as below: Users:

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

Login_track table:

  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
    4      William       NULL

Note : User id 3 (Anderson) and 4 (William) has no records in login_track table, even though those two should also list in the final result with NULL value to login_date column.

Thanks.

can you show your try?

Jenish
– Jenish

2017-06-30 07:36:24 +00:00
Commented Jun 30, 2017 at 7:36 — Jenish
– Jenish, Commented Jun 30, 2017 at 7:36

Prabhat G · Accepted Answer · 2017-06-30 10:26:48Z

1

It's a bit tricky as MySQL don't have ROW_NUMBER() function. Hence with a simple trick, try this :

Select u.user_id, u.name, GROUP_CONCAT(DISTINCT iq.login_date) login_date
FROM(
select user_id,  login_date
from 
(
   select user_id, login_date,
      (@num:=if(@group = user_id, @num +1, if(@group := user_id, 1, 1))) row_number 
  from Login_track 
  CROSS JOIN (select @num:=0, @group:=null) c
  order by user_id, login_date DESC
) as x 
where x.row_number <= 2
    )iq
right join users u on u.user_id = iq.user_id
GROUP BY u.user_id

output :

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

edited Jun 30, 2017 at 10:26

answered Jun 30, 2017 at 8:42

Prabhat G

3,0492 gold badges27 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

orangetime Over a year ago

I need all the records from user table, from your solution users without records in login_track is not listing.

Prabhat G Over a year ago

Share more sample data so I can verify ?

Jenish · Accepted Answer · 2017-06-30 08:07:39Z

1

You can user GROUP_CONCAT()

SELECT substring_index(group_concat(login_date SEPARATOR ','), ',', 2) AS login_date FROM `Users` LEFT JOIN `Login_track` ON `Login_track`.`user_id` = `users`.`id` GROUP BY `users`.`id`

edited Jun 30, 2017 at 8:07

answered Jun 30, 2017 at 7:39

Jenish

5334 silver badges16 bronze badges

7 Comments

Ramin Darvishov Over a year ago

He want only 2 login_date not all

orangetime Over a year ago

@jenish, users may have zero or many records in login track table.

Jenish Over a year ago

you can set required condition in where condition for further improve this query @orangetime

Jenish Over a year ago

so you don't need those data whose login_data is null... Am i right?

orangetime Over a year ago

Required login_date as NULL, if login track not exists for any user.

|

Collectives™ on Stack Overflow

Mysql Select recent 2 records for each group[user]

2 Answers 2

2 Comments

7 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

7 Comments

Related