Get most recent row for given ID

by

Use the aggregate MAX(signin) grouped by id. This will list the most recent signin for each id.

SELECT 
 id, 
 MAX(signin) AS most_recent_signin
FROM tbl
GROUP BY id

To get the whole single record, perform an INNER JOIN against a subquery which returns only the MAX(signin) per id.

SELECT 
  tbl.id,
  signin,
  signout
FROM tbl
  INNER JOIN (
    SELECT id, MAX(signin) AS maxsign FROM tbl GROUP BY id
  ) ms ON tbl.id = ms.id AND signin = maxsign
WHERE tbl.id=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. Select row with most recent date per user
  9. Get the latest date from grouped MySQL data
  10. In SQL, how to select the top 2 rows for each group
  11. How to get the latest record in each group using GROUP BY? [duplicate]
  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. how to delete data from table A besides the customer service and director data in table B?
  18. SQL split values to multiple rows
  19. How to populate a table with a range of dates?
  20. MYSQL OR vs IN performance
  21. ERROR 1452: Cannot add or update a child row: a foreign key constraint fails
  22. MySQL: View with Subquery in the FROM Clause Limitation
  23. MySql export schema without data
  24. MySQL Pivot Table Column Data as Rows
  25. updating columns with a sequence number mysql
  26. MySQL, Concatenate two columns
  27. Select a column with a keyword name
  28. Sum results of a few queries and then find top 5 in SQL
  29. ERROR 1148: The used command is not allowed with this MySQL version
  30. Use Composite Primary Key as Foreign Key

Leave a Comment