Select row with most recent date per user

by

Query:

SQLFIDDLEExample

SELECT t1.*
FROM lms_attendance t1
WHERE 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 |

Note that if a user has multiple records with the same “maximum” time, the query above will return more than one record. If you only want 1 record per user, use the query below:

SQLFIDDLEExample

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)

More Related Contents:

  1. SQL select only rows with max value on a column [duplicate]
  2. Using LIMIT within GROUP BY to get N results per group?
  3. ROW_NUMBER() in MySQL
  4. Get top n records for each group of grouped results
  5. How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?
  6. Get records with max value for each group of grouped SQL results
  7. Retrieving the last record in each group – MySQL
  8. Get the latest date from grouped MySQL data
  9. In SQL, how to select the top 2 rows for each group
  10. How to get the latest record in each group using GROUP BY? [duplicate]
  11. Get most recent row for given ID
  12. Using ORDER BY and GROUP BY together
  13. How to select the most recent set of dated records from a mysql table
  14. SELECT query return 1 row from each group
  15. How to select data where a field has a min value in MySQL?
  16. How do I limit the number of rows per field value in SQL?
  17. MySQL pivot row into dynamic number of columns
  18. Count the number of occurrences of a string in a VARCHAR field?
  19. Groupwise maximum
  20. Filter by COUNT(*)?
  21. How to select rows that have current day’s timestamp?
  22. MySQL concatenation operator
  23. SQL Group By with an Order By
  24. MySql – Way to update portion of a string?
  25. Using reserved words in column names
  26. MySQL INNER JOIN select only one row from second table
  27. Storing IPv6 Addresses in MySQL
  28. Insert/ Update random date in MySQL
  29. Get total hours worked in a day mysql
  30. MySQL Multiple Joins in one query?

Leave a Comment