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
