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. Thanks for your help -- Vincent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
