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

User · Answer

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

User · Answer

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

User · Answer

try this   SELECT model  date FROM doc WHERE date    SELECT MAX date  FROM doc GROUP BY model LIMIT 0  1  GROUP BY model

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

And why not to use this    SELECT model  date FROM doc ORDER BY date DESC LIMIT 1

[mysql] Get the latest date from grouped MySQL data

Examples related to mysql

Examples related to sql

Examples related to date

Examples related to greatest-n-per-group