Get the latest date from grouped MySQL data

Question

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

score 163 · Accepted Answer · edited Jan 29 '21 at 12:45

163

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;

edited Jan 29 '21 at 12:45

oguz ismail

1
16
47
69

answered Dec 12 '11 at 05:48

phatfingers

9,770
3
30
44

4

I think the right sql would be： `SELECT model, date FROM doc WHERE date IN (SELECT max(date) FROM doc group by model)`, should add group by at last. – hahakubile Mar 14 '14 at 07:53
You'll need this if you have other column in the table. http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql – haudoing May 20 '15 at 10:41
I was trying `SELECT model, date FROM doc GROUP BY model ORDER BY date DESC` but the order seems to be ignored here. – Radek Pech Nov 23 '15 at 19:30
Consider what it means to group by model. You're asking the query to consolidate multiple dates matching each model into one entry. You need to specify a function like MIN(date) or MAX(date) to determine which one date to display from the many associated with model. – phatfingers Nov 23 '15 at 23:06
I recommend using `MAX(date) as max_date`, so that you can use `ORDER BY max_date DESC` – MrMartin Sep 26 '17 at 11:38
1

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. – Iman Sedighi Feb 28 '18 at 20:24
I added a third option because many comments throughout seemed to want to get at individual records instead of just a summary, and several wrong answers were posted. – phatfingers Oct 11 '19 at 20:29
You just saved me, I've been looking for a solution for a long time and I just needed to link the subquery with the query. d2.model = d.model Thanks a lot. – Sylar Mar 18 '21 at 10:48

score 21 · Answer 2 · answered Dec 12 '11 at 05:40

21

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);

answered Dec 12 '11 at 05:40

gprathour

14,813
5
66
90

As the `GROUP BY` is mendatory to get **all records**, this should be the accepted answer. – Meloman Sep 19 '19 at 08:24
4

Consider the dataset, `('foo', '2019-10-01'), ('foo', '2019-10-10'), ('bar', '2019-10-01')` Using `GROUP BY` in the subselect would return an unwanted `foo` row for '2019-10-01' because it matches the same date as the `bar` max date. – phatfingers Oct 11 '19 at 19:49

score 17 · Answer 3 · answered Jun 05 '17 at 13:39

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

score 1 · Answer 4 · answered Dec 12 '11 at 05:41

1

try this:

SELECT model, date
FROM doc
WHERE date = (SELECT MAX(date)
FROM doc GROUP BY model LIMIT 0, 1)
GROUP BY model

answered Dec 12 '11 at 05:41

Christopher Pelayo

792
11
30

score 0 · Answer 5 · answered Feb 28 '18 at 20:23

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

score -3 · Answer 6 · answered Oct 09 '14 at 14:06

-3

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.

answered Oct 09 '14 at 14:06

Aldarien

173
2
6

1

this is not correct, if you GROUP BY model you have to apply an aggregate function on the other columns in select. – cebe Dec 03 '15 at 20:05

score -3 · Answer 7 · answered Jul 27 '17 at 16:21

-3

And why not to use this ?

SELECT model, date FROM doc ORDER BY date DESC LIMIT 1

answered Jul 27 '17 at 16:21

Vasekdvor

27
1
4

you won't get the max or latest date using it. just sorting the date – nunu Jul 28 '17 at 05:01
If i sort the date in descending order and then get only the first one, then i have the newest date. – Vasekdvor Jul 30 '17 at 12:23
yes, of course. But what i want to do is to show the max date for each model. So i don't need to show a lot of record. – nunu Jul 31 '17 at 01:43
also, there's a nasty bug on mysql for "order by <..> desc limit 1" – dresende Jan 02 '18 at 14:17

Get the latest date from grouped MySQL data

7 Answers7

Linked

Related