Get the latest date from grouped MySQL data

by

Are you looking for the max date for each model?

SELECT model, max(date) FROM doc
GROUP BY model

If you’re looking for all models matching the max date of the entire table…

SELECT model, date FROM doc
WHERE date IN (SELECT max(date) FROM doc)

[— Added —]

For those who want to display details from every record matching the latest date within each model group (not summary data, as asked for in the OP):

SELECT d.model, d.date, d.color, d.etc FROM doc d
WHERE d.date IN (SELECT max(d2.date) FROM doc d2 WHERE d2.model=d.model)

MySQL 8.0 and newer supports the OVER clause, producing the same results a bit faster for larger data sets.

SELECT model, date, color, etc FROM (SELECT model, date, color, etc, 
  max(date) OVER (PARTITION BY model) max_date FROM doc) predoc 
WHERE date=max_date;

More Related Contents:

  1. How to select the most recent set of dated records from a mysql table
  2. SQL select only rows with max value on a column [duplicate]
  3. Using LIMIT within GROUP BY to get N results per group?
  4. ROW_NUMBER() in MySQL
  5. Get top n records for each group of grouped results
  6. How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?
  7. Get records with max value for each group of grouped SQL results
  8. Left Outer Join doesn’t return all rows from my left table?
  9. Get a list of dates between two dates
  10. How to populate a table with a range of dates?
  11. MySQL Query GROUP BY day / month / year
  12. Error in MySQL when setting default value for DATE or DATETIME
  13. Retrieving the last record in each group – MySQL
  14. Select row with most recent date per user
  15. Difference between two dates in MySQL
  16. In SQL, how to select the top 2 rows for each group
  17. How to get the latest record in each group using GROUP BY? [duplicate]
  18. Get most recent row for given ID
  19. Using ORDER BY and GROUP BY together
  20. Compare only day and month with date field in mysql
  21. SELECT query return 1 row from each group
  22. Insert/ Update random date in MySQL
  23. How to select data where a field has a min value in MySQL?
  24. #1292 – Incorrect date value: ‘0000-00-00’ [duplicate]
  25. SQL date format convert? [dd.mm.yy to YYYY-MM-DD]
  26. How do I limit the number of rows per field value in SQL?
  27. How do I extract Month and Year in a MySQL date and compare them?
  28. SQL Print the name of all Employees together with the name of their supervisor
  29. Invalid default value for ‘dateAdded’
  30. MySQL: Computed Column

Leave a Comment