Hi Vincent! > 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 :
There is no simple solution to your problem. Strictly speaking, it's forbidden to name a column in the SELECT clause which is NOT on the GROUP BY clause. (Read chapter: "13.9.3 GROUP BY with Hidden Fields" from the MySQL Documentation.). Anyway, MySQL is relaxed from ANSI SQL in such a way it's permitted to include a column which is not in the GROUP BY clause (hidden columns). The values returned from such a query are "unpredictable". I started saying there is no _simple_ solution. However, you can think a little bit and your problem goes away. I can think of two solutions to your problem which neither involves a subquery. You are lucky in that you know for sure what your last value is (you do have a "date" field"). You already know the value of using MAX(date) in the SELECT. You can concatenate the date column with the version, and return a "SUBSTRING" from that operation, something like this: SELECT SUBSTRING_INDEX(MAX(CONCAT(date, ' ', version)), ' ', -1) AS correct_version, location, version AS wrong_version FROM cities WHERE content='ALPHA' GROUP BY location You will get the following result set: (Notice the "correct_version" column). +-----------------+----------+---------------+ | correct_version | location | wrong_version | +-----------------+----------+---------------+ | 10 | NEW-YORK | 11 | | 10 | PARIS | 10 | | 11 | TOKYO | 10 | +-----------------+----------+---------------+ There is another option, however, and this one is what I like most. In two words, you can join the table with itself, like this: SELECT c1.date, c1.location, c1.version FROM cities c1 LEFT JOIN cities c2 ON c1.location=c2.location AND c1.content=c2.content AND c2.date>c1.date WHERE c2.id IS NULL AND c1.content = 'ALPHA' What we have done here is first LEFT JOIN the table (cities in my example) with itself so that we have in the left part those rows which have the maximum date and with NULL on the right as the value of c2. We then select those saying "WHERE c2.id IS NULL". The last operation is selecting the rows for an 'ALPHA' content. Notice that with this version we also eliminate the GROUP BY, since all rows returned from the join are unique. The result set returned with the last example is this: +------------+----------+---------+ | date | location | version | +------------+----------+---------+ | 2004-09-16 | PARIS | 10 | | 2004-09-16 | NEW-YORK | 10 | | 2004-09-15 | TOKYO | 11 | +------------+----------+---------+ Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]