Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get the latest date from grouped MySQL data

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
like image 776
nunu Avatar asked Dec 12 '11 05:12

nunu


People also ask

How do I get the latest record of each ID in SQL?

Since for each row in the subquery result MySQL will need a single fetch based on primary key, the subquery will be put first in the join and the rows will be output in the order of the ids in the subquery (if we omit explicit ORDER BY for the join)

How do I get the latest data from a table in SQL?

We can use the ORDER BY statement and LIMT clause to extract the last data. The basic idea is to sort the sort the table in descending order and then we will limit the number of rows to 1. In this way, we will get the output as the last row of the table. And then we can select the entry which we want to retrieve.

How can I get the latest record in database based on datetime?

If you are using ->latest()->get() then it will get records base on created_at, if we get records base on created_at then how about order by id desc both work as same. if you need by other column datetime then you can use ->orderBy('columnName','desc/asc')->get() otherwise you can use latest() function.


Video Answer


3 Answers

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; 
like image 189
phatfingers Avatar answered Sep 25 '22 13:09

phatfingers


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); 
like image 26
gprathour Avatar answered Sep 23 '22 13:09

gprathour


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
like image 43
Madhu Kiran Seelam Avatar answered Sep 24 '22 13:09

Madhu Kiran Seelam