thanks zhu. i found a solution using your suggestion and the manual:
http://dev.mysql.com/doc/refman/5.0/en/union.html
(select 1 as one, brand, max(year) two from cars group by brand)
union
(select 2 as one, brand, year two from cars) order by one, two desc;
the important question now is performance.
if each select takes 2 seconds, will the union of them take 2+2=4
seconds, or will it be quicker?
2009/4/26 zhu dingze <mysql.lists@stripped>:
> select brand, max(year) from cars group by year order by year
> union
> select brand, year from cars order by year
>
> 2009/4/25 Olav Mørkrid <olav.morkrid@stripped>
>>
>> Say I have a table with any number of car brands, with belonging street
>> dates.
>>
>> Is it possible to do the following type of sort in a single query?
>>
>> 1. First I want the newest model of all car brands, but only one of
>> each brand! And they should be ordered by year
>>
>> 2. Then I want the cars that weren't the newest of their brand, also
>> ordered by year
>>
>> Currently, I do two similar queries where I use "group by" on the
>> first batch, but since each query is rather heavy, I would like to
>> reduce them to one query if possible.
>>
>> select brand, year from cars order by [WHICH METHOD?]
>>
>> Ferrari | 2008
>> Porsche | 2005
>> Jaguar | 1997
>> ----------+-------
>> Ferrari | 2006
>> Porsche | 2004
>> Ferrari | 2003
>> Jaguar | 1995
>> Jaguar | 1992
>> Porsche | 1981
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=1
>>
>
>
>
> --
> Dingze Zhu
>
> We are running the best Chinese MySQL and Solaris Community in China.
> Welcome to visit http://www.mysqlsystems.com
>
