Group by like row filtering

Question

Lets say I have a table describing cars with make, model, year and some other columns.

From that, I would like to get one full row of each make and model for latest year.

Or to put it in another way. Each row would have unique combination of make and model with other data from corresponding row with largest value of year.

Standard SQL solution would be great.

Answer 1

MySQL solution:

SELECT * FROM cars GROUP NY CONCAT(make, "-", model) ORDER BY year DESC;

Answer 2

select t1.make, t1.model, t1.year, t1.other_cols
from table t1
where year = (select max(year) from table t2
              where t2.make = t1.make
              and t2.model = t1.model
             );

Answer 3

This should work most anywhere:

SELECT c1.* 
FROM cars c1
INNER JOIN 
  (
     SELECT Make, Model, Max(Year) AS Year
     FROM cars
     GROUP BY Make, Model
  ) c2 ON c1.Make=c2.Make AND c1.Model=c2.Model, c1.Year=c2.Year

The main caveat is that Year is often a reserved word (function name) and the means for escaping reserved words vary by platorm. To fix that, rename the year column to something like ModelYear.