select F.u_n,F.c_n,F.dt
from
(select A.id,A.user_id,B.name as u_n, C.name as c_n, A.date as dt
from login A
left join user B
on A.user_id = B.id
left join client C
on A.client_id = C.id) as F

left join 

(select user_id,max(dt) as mdt from 
(select A.id,A.user_id,B.name as u_n, C.name as c_n, A.date as dt
from login A
left join user B
on A.user_id = B.id
left join client C
on A.client_id = C.id) as D
group by user_id) as E

on F.user_id = E.user_id
where F.dt = E.mdt
order by F.u_n