Somepub Somepub - 8 months ago 36
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.event,m.username
FROM sys_log s join
users m
on s.username_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.event, m.username
from sys_log s join
     users m
     on s.sys_auth_id =
where = (select max( from sys_log s2 where s2.sys_auth_id = s.sys_auth_id);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download