Menu
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
776
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)
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.
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.
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; 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); 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
