----- Original Message ----- From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, September 16, 2004 3:05 AM Subject: Query with group by
> Hello all, > > i'm trying to built a query that report me the latest tuple for a given > field. > I explain myself a bit. Let's say i have a table such this : > > +----+------------+---------+----------+---------+ > | id | date | content | location | version | > +----+------------+---------+----------+---------+ > | 1 | 2004-09-14 | ALPHA | PARIS | 10 | > | 2 | 2004-09-15 | ALPHA | PARIS | 11 | > | 3 | 2004-09-16 | ALPHA | PARIS | 10 | > | 4 | 2004-09-14 | ALPHA | NEW-YORK | 11 | > | 5 | 2004-09-15 | ALPHA | NEW-YORK | 11 | > | 6 | 2004-09-16 | ALPHA | NEW-YORK | 10 | > | 7 | 2004-09-14 | ALPHA | TOKYO | 10 | > | 8 | 2004-09-15 | ALPHA | TOKYO | 11 | > | 9 | 2004-09-16 | BETA | TOKYO | 10 | > +----+------------+---------+----------+---------+ > > Then, i'm trying to get, for "ALPHA" content, the last (most recent) tuple > for each location, with their associated version. > What i should have in the result set : > > +------------+---------+----------+---------+ > | 2004-09-16 | ALPHA | PARIS | 10 | > | 2004-09-16 | ALPHA | NEW-YORK | 10 | > | 2004-09-15 | ALPHA | TOKYO | 11 | > +------------+---------+----------+---------+ > > I tried with max(date) but i get this : > > mysql> select max(date), location, version from temp where content="ALPHA" > group by location; > +------------+----------+---------+ > | max(date) | location | version | > +------------+----------+---------+ > | 2004-09-16 | NEW-YORK | 11 | > | 2004-09-16 | PARIS | 10 | > | 2004-09-15 | TOKYO | 10 | > +------------+----------+---------+ > > Because the GROUP BY statement get the first tuple by default? > distinct(max(date)) do the same result. > > How them can i get the correct result set? > Note that we cannot guess if the version is increasing or deacreasing. > Which version of MySQL are you running? I'm having trouble thinking of a solution that doesn't involve a subquery but subqueries aren't supported until version 4.1.x; I don't want to give you a subquery if you can't run it. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
