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.
