Get the latest date from grouped MySQL data
MysqlSqlDateGreatest N-per-GroupMysql Problem Overview
I have the following data in my database:
|NO | model | date |
+---+-------+----------+
|1 | bee |2011-12-01|
|2 | bee |2011-12-05|
|3 | bee |2011-12-12|
|4 | tar |2011-12-13|
I want to get the latest date of each model group:
| model | date |
+-------+----------+
| bee |2011-12-12|
| tar |2011-12-13|
I tried:
SELECT model, date
FROM doc
WHERE date ........????? //what is the next?
GROUP BY model
Mysql Solutions
Solution 1 - Mysql
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;
Solution 2 - Mysql
You can try using max() in subquery, something like this :
SELECT model, date
FROM doc
WHERE date in (SELECT MAX(date) from doc GROUP BY model);
Solution 3 - Mysql
Subquery giving dates. We are not linking with the model. So below query solves the problem.
If there are duplicate dates/model can be avoided by the following query.
select t.model, t.date
from doc t
inner join (select model, max(date) as MaxDate from doc group by model)
tm on t.model = tm.model and t.date = tm.MaxDate
Solution 4 - Mysql
try this:
SELECT model, date
FROM doc
WHERE date = (SELECT MAX(date)
FROM doc GROUP BY model LIMIT 0, 1)
GROUP BY model
Solution 5 - Mysql
Using max(date) didn't solve my problem as there is no assurance that other columns will be from the same row as the max(date) is. Instead of that this one solved my problem and sorted group by in a correct order and values of other columns are from the same row as the max date is:
SELECT model, date
FROM (SELECT * FROM doc ORDER BY date DESC) as sortedTable
GROUP BY model
Solution 6 - Mysql
This should work:
SELECT model, date FROM doc GROUP BY model ORDER BY date DESC
It just sort the dates from last to first and by grouping it only grabs the first one.
Solution 7 - Mysql
And why not to use this ?
SELECT model, date FROM doc ORDER BY date DESC LIMIT 1