List:General Discussion« Previous MessageNext Message »
From:Olav Mørkrid Date:April 27 2009 7:11am
Subject:Re: Ordering cars
View as plain text  
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
>
Thread
Ordering carsOlav Mørkrid25 Apr
  • Re: Ordering carszhu dingze26 Apr
    • Re: Ordering carsOlav Mørkrid27 Apr