SQL - Limit results on groups

I have the following table :

Car      | color    | year
---------+----------+------
mercedes | blue     | 1991
mercedes | yellow   | 1993
mercedes | blue     | 1996
me开发者_如何学JAVArcedes | red      | 1998
renaud   | blue     | 1991
renaud   | yellow   | 1993
renaud   | blue     | 1996
renaud   | red      | 1998

I am looking for a query which permits to retrieve the oldest year row of each car type. In this case, it should give me :

Car       | color | year
----------+-------+------
mercedes  | blue  | 1991
renaud    | blue  | 1991

I have tried with ORDER BY + LIMIT, HAVING, GROUP BY.... But it always add extra rows which I don't need (I have to iterate over the result set to create a sub result).

Do you have ideas of how I could do that ?

Thanks a lot for your helpfull hints !

Kheraud

---

nested select - something like this:

select car, max(year) yr
from mytable
group by car

this gives the proper year for each car. then you want to add a color (not specified what to do if there are more than one in that year for that car...)

select a.car, a.color, a.yr
from (
select car, max(year) yr
from mytable
group by car
) a
, mytable
where mytable.car = a.car
and mytable.year = a.yr

---

select car, color, Max(Year) from table_name group by car,color