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.li...@gmail.com>: > 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.mork...@gmail.com> >> >> 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=mysql.li...@gmail.com >> > > > > -- > Dingze Zhu > > We are running the best Chinese MySQL and Solaris Community in China. > Welcome to visit http://www.mysqlsystems.com > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
