Select row with most recent date per user
Query:
SELECT t1.*FROM lms_attendance t1WHERE t1.time = (SELECT MAX(t2.time) FROM lms_attendance t2 WHERE t2.user = t1.user)Result:
| ID | USER | TIME | IO |--------------------------------| 2 | 9 | 1370931664 | out || 3 | 6 | 1370932128 | out || 5 | 12 | 1370933037 | in |Solution which gonna work everytime:
SELECT t1.*FROM lms_attendance t1WHERE t1.id = (SELECT t2.id FROM lms_attendance t2 WHERE t2.user = t1.user ORDER BY t2.id DESC LIMIT 1)
No need to trying reinvent the wheel, as this is common greatest-n-per-group problem. Very nice solution is presented.
I prefer the most simplistic solution (see SQLFiddle, updated Justin's) without subqueries (thus easy to use in views):
SELECT t1.*FROM lms_attendance AS t1LEFT OUTER JOIN lms_attendance AS t2 ON t1.user = t2.user AND (t1.time < t2.time OR (t1.time = t2.time AND t1.Id < t2.Id))WHERE t2.user IS NULLThis also works in a case where there are two different records with the same greatest value within the same group - thanks to the trick with (t1.time = t2.time AND t1.Id < t2.Id). All I am doing here is to assure that in case when two records of the same user have same time only one is chosen. Doesn't actually matter if the criteria is Id or something else - basically any criteria that is guaranteed to be unique would make the job here.
Based in @TMS answer, I like it because there's no need for subqueries but I think ommiting the 'OR' part will be sufficient and much simpler to understand and read.
SELECT t1.*FROM lms_attendance AS t1LEFT JOIN lms_attendance AS t2 ON t1.user = t2.user AND t1.time < t2.timeWHERE t2.user IS NULLif you are not interested in rows with null times you can filter them in the WHERE clause:
SELECT t1.*FROM lms_attendance AS t1LEFT JOIN lms_attendance AS t2 ON t1.user = t2.user AND t1.time < t2.timeWHERE t2.user IS NULL and t1.time IS NOT NULL