Somepub Somepub - 5 months ago 25
MySQL Question

how to get the latest record of loginevent from each user?

This question may already asked, but my question refers to two separate tables. I have only seen the answer when using same table with date row on it.
I have two tables, one is the user table and the other is login_log table.
I want to get the event from latest date record and from each user.
I have already made sql query, but cant understand how can I pick the max(date) from separate user.

SELECT s.username_id,s.date,s.event,m.username
FROM sys_log s join
users m
on s.username_id = m.id

id, username
---------
1 test
2 test2

id, username_id, date, event
------------------------------------------
1 1 1/1/2017 22:10:11 logout
2 1 1/1/2017 22:09:11 login
3 2 1/1/2017 21:05:11 logout
4 2 1/1/2017 21:02:11 login


the output should be like this

id, username, event ,date
-------------------------
1 test logout xxx
2 test2 logout xxx

Answer Source

Does this do what you want?

select s.username_id, s.date, s.event, m.username
from sys_log s join
     users m
     on s.sys_auth_id = m.id
where s.date = (select max(s2.date) from sys_log s2 where s2.sys_auth_id = s.sys_auth_id);