----- Original Message -----
From: <Vincent.Badier@stripped>
To: <mysql@stripped>
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
