Select newest records from table

Question

I have table cars:

Id int,
Model nvarchar(max),
DateOfProduction (datetime).

And data like:

1 BMW X5 1.1.2010
2 BMW X5 1.1.2009
3 BMW X3 1.1.2008
4 BMW X3 1.1.2007
5 BMW X7 1.1.2005

And I want to get newest car for each model:

1 BMW X5 1.1.2010
3 BMW X3 1.1.2008
5 BMW X7 1.1.2005

I can't cope with this:/

Could you help me ?

Answer 1

SELECT MIN(Id), Model, MAX(DateOfProduction) FROM Table
GROUP by Model

This will work for the data given.

However, it assumes that you want the oldest Id and the newest date for that model

Answer 2

For your given table structure, following SQL returns what you need but you might consider changing your table structure into something like this

cars (carid, model)  
production (carid, DateOfProduction)

SQL Statement

SELECT c.*
FROM   dbo.Cars c
       INNER JOIN (
         SELECT Model
                DateOfProduction = MAX(DateOfProduction)
         FROM   dbo.cars
         GROUP BY
                Model
       ) dm ON dm.DateOfProduction = c.DateOfProduction
               AND dm.Model = c.Model