How to Retrieve the Most Recent Record for Each User in a SQL Database?

Get the latest user record for each user

Suppose you have a table containing a user's check-in and check-out times ("lms_attendance"). To gain insight into their activity, you might want a view that shows each user's latest activity (check-ins or check-outs).

To achieve this in SQL you can use the following query:

<code class="language-sql">SELECT t1.*
FROM lms_attendance t1
WHERE t1.time = (SELECT MAX(t2.time)
                 FROM lms_attendance t2
                 WHERE t2.user = t1.user);</code>

This query uses a subquery to determine the maximum time for each user, ensuring the most recent activity is captured. The result will be similar to the expected output:

<code>| ID | USER |       TIME |  IO |
--------------------------------
|  2 |    9 | 1370931664 | out |
|  3 |    6 | 1370932128 | out |
|  5 |   12 | 1370933037 |  in |</code>

However, if you only need one record per user, modify the query as follows:

<code class="language-sql">SELECT t1.*
FROM lms_attendance t1
WHERE t1.id = (SELECT t2.id
                 FROM lms_attendance t2
                 WHERE t2.user = t1.user            
                 ORDER BY t2.id DESC
                 LIMIT 1);</code>

The above is the detailed content of How to Retrieve the Most Recent Record for Each User in a SQL Database?. For more information, please follow other related articles on the PHP Chinese website!