MySQL: Grouping and counting multiple occurences, then only display the highest amount per individual combination

Question

I'm currently trying to determine which user owns which PC in my company. We're logging every login activity in a MySQL table.

Now we only have information about which User is using which client, but given that users may also use other machines, I need to check which user has the highest amount of logins on that specific machine.

Table: log
+----+-----------+------+----------------+
| ID |  Client   | user |   timestamp    | 
+----+-----------+------+----------------+
|  1 | hostnamea | ab   | xx.xx.xx xx:xx |
|  2 | hostnameb | cd   | xx.xx.xx xx:xx |
|  3 | hostnameb | ab   | xx.xx.xx xx:xx |
|  4 | hostnameb | ab   | xx.xx.xx xx:xx |
|  5 | hostnamec | ab   | xx.xx.xx xx:xx |
|  6 | hostnamec | ef   | xx.xx.xx xx:xx |
|  7 | hostnamec | ef   | xx.xx.xx xx:xx |
+----+-----------+------+----------------+
(timestamp is actually unimportant,
 but maybe someone has an awesome optimization idea which includes the timestamp,
 so I leave it here)

In this case, hostname user "ef" becomes owner of "hostnamec"

I already got so far that I can display a "top logins" list:

SELECT `user`, `client`, COUNT(*) logins 
FROM `log` 
WHERE `client` LIKE '_%' AND timestamp > "2016-09-00 00:00:00"  # (filter out garbage and old machines)
GROUP BY `client, `user`
HAVING COUNT(*) > 10     (If there are less than 10 logins, the machine isn't being used anyways)
ORDER BY `user`;

This returns user, client and a number of logins, which, I hope, specify how often a specific user logged into a machine.

I don't really know how to access and use the extra "logins" row generated by the count.

My goal is something like this:

+-----------+------+
|  Client   | user |
+-----------+------+
| hostnamea | ab   | (1xab, nothing else: ab wins)
| hostnameb | ab   | (2x ab,1x cd: ab wins)
| hostnamec | ef   | (2x ef, 1x ab: ef wins)
+-----------+------+

My next step would be an Update on another existing table that has hostnames, but it's missing users

UPDATE hosts
INNER JOIN query(output of first query) USING (client)
SET hosts.user = query.user

(If that makes sense)

Can anyone assist me in my first query? I already found some solutions including Oracle functions, or tables that are slightly diffently designed.

It's been long time since I had to do more than simple Selects, updates and inserts with SQL; to be honest, I got kinda confused by what I found, so I'm asking my own question

Answer 1

You can do this by filtering in the where clause. Here is one way:

select l.*
from log l
where l.timestamp = (select max(l2.timestamp) 
                     from log l2
                     where l2.user = l.user
                    );

This is easily generalizable to get the owner as of any given date:

select l.*
from log l
where l.timestamp = (select max(l2.timestamp) 
                     from log l2
                     where l2.timestamp <= $AsOfDate and l2.user = l.user
                    );

Answer 2

Try this query

SELECT
   Client 
 , SUBSTRING_INDEX(GROUP_CONCAT(user ORDER BY Client DESC), ',', 1) user
FROM 
 log
GROUP BY 
 Client
ORDER BY 
 Client ASC