SQL question: I have a table 'scans' with columns (machine_id, timestamp, user, result). I wish to find, with a single query, when the last scan was done for each machine and which user did the scan. How should I structure my query to do this?
I know I can do `SELECT machine_id, max(timestamp) FROM scans GROUP BY machine_id;` to get the timestamp of the latest scan for each machine, but how do I also get the user who performed that scan?
I can think of one way - use a subquery to get the machine ID and timestamp-of-latest-scan, concatenate them together, then use a `WHERE ... IN` clause on the main query to select the corresponding rows, and get the desired columns as normal. But that seems really awkward and inefficient.
Something like this, except there'd probably need to be a bunch of typecasting to make the concatenation work: SELECT machine_id, timestamp, user FROM scans WHERE CONCAT(machine_id, max(timestamp)) in (SELECT CONCAT(machine_id, max(timestamp)) FROM scans GROUP BY machine_id`);