SQL
Retrieve Latest Record for Each Group
Efficiently fetch the most recent entry for each distinct group within a table, such as the last login for every user, using a common subquery pattern.
SELECT t1.user_id, t1.login_timestamp, t1.ip_address
FROM user_logins t1
INNER JOIN (
SELECT user_id, MAX(login_timestamp) AS latest_login
FROM user_logins
GROUP BY user_id
) AS t2
ON t1.user_id = t2.user_id AND t1.login_timestamp = t2.latest_login;
How it works: This snippet retrieves the complete record corresponding to the latest entry for each distinct group (e.g., the most recent login for every user). The inner subquery identifies the maximum `login_timestamp` for each `user_id`. The outer query then joins back to this result, selecting the full row from `user_logins` where both the `user_id` and `login_timestamp` match the latest found in the subquery.