WITH t1 as(
SELECT U.name as u_n, C.name as c_n, l.date as date,
rank()over(partition by U.name Order by l.date desc) as rnk
FROM login l 
LEFT JOIN user U on l.user_id=U.id 
LEFT JOIN client C on l.client_id = C.id
ORDER BY U.name)

SELECT u_n, group_concat(c_n), date
FROM t1
WHERE rnk = 1
GROUP BY u_n, date