Assuming you have a
PRIMARY KEY or otherwise
UNIQUE KEY called
id (replace with whatever it's called in your table), consider the following query:
GROUP_CONCAT(top_id_per_group) AS top_ids
SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY lastvisit DESC), ',', 1) AS top_id_per_group
The idea is to get, per
id where the
lastvisit is most recent. Then, get all row data for those
The above is a simplification of Selecting Top N Records Per Group, where
N = 1.
Another way to solve it is described in Selecting Non Aggregated Column Data in GROUP BY. The query presented in this post actually provides better performance than the above, but uses some parsing and casting to get the final results (all columns parsed as strings, then cast back to their original types). Also see this post. Apologies for the many links, but each post presents with a different solution to the problem.