Hope you could do-

Possess a table like so:

Car_ID    Brand    Model      Year
----------------------------------
   1      Ford      Focus       2012
   1      Ford      Mustang     1992
   2      Toyota    Yaris       2005
   3      BMW       1s          1995
   4      VW        Polo        2010
   4      VW        Golf        2002
   4      VW        Beetle      2007

And wish it ordering such as this:

Car_ID    Brand    Model      Year
----------------------------------
   1      Ford      Focus       2012
   1      Ford      Mustang     1992

   4      VW        Polo        2010
   4      VW        Beetle      2007
   4      VW        Golf        2002

   2      Toyota    Yaris       2005

   3      BMW       1s          1995
  • Therefore the brand using the latest model is first, then the relaxation of the brand's models so as of latest.

  • Next may be the brand using the second latest model and also the relaxation of cars of this brand, latest first

  • And so forth.

Any ideas if you can do this?

--ANSWER-- - my answer was wrong, will publish when I test more

mysql>     SELECT cars.*
    -> 
    ->       FROM cars
    ->       JOIN ( SELECT Name_ID, MAX(Year) AS max_year 
    ->              FROM cars 
    ->              GROUP BY Name_ID )
    ->         AS dt_max
    ->      USING (Name_ID)
    -> 
    ->   ORDER BY max_year DESC, Name_ID, Year DESC;
+---------+--------+---------+------+
| Name_ID | Brand  | Model   | Year |
+---------+--------+---------+------+
|       1 | Ford   | Focus   | 2012 |
|       1 | Ford   | Mustang | 1992 |
|       4 | VW     | Polo    | 2010 |
|       4 | VW     | Beetle  | 2007 |
|       4 | VW     | Golf    | 2002 |
|       2 | Toyota | Yaris   | 2005 |
|       3 | BMW    | 1s      | 1995 |
+---------+--------+---------+------+
7 rows in set (0.00 sec)