Rhino wrote:
> I agree that Michael's solution with the temporary tables is the best I have
> seen so far.
I can't take much credit. It's just an adaptation of the solution in the
manual.
> I am running MySQL 4.0.15 so I don't have any subquery capability. As a
> result, I went to DB2 to come up with one solution that does give the right
> answer via a subquery:
>
> select content, location, version, date
> from versions
> where concat(location, char(date)) in
> (select concat(location, char(max(date)))
> from versions
> where content = 'ALPHA'
> group by location);
Really? That didn't work for me in mysql 4.1.4a-gamma. I got
+---------+----------+---------+------------+
| content | location | version | date |
+---------+----------+---------+------------+
| ALPHA | PARIS | 10 | 2004-09-14 |
| ALPHA | PARIS | 11 | 2004-09-15 |
| ALPHA | PARIS | 10 | 2004-09-16 |
| ALPHA | NEW-YORK | 11 | 2004-09-14 |
| ALPHA | NEW-YORK | 11 | 2004-09-15 |
| ALPHA | NEW-YORK | 10 | 2004-09-16 |
| ALPHA | TOKYO | 10 | 2004-09-14 |
| ALPHA | TOKYO | 11 | 2004-09-15 |
| BETA | TOKYO | 10 | 2004-09-16 |
+---------+----------+---------+------------+
9 rows in set (0.31 sec)
> I don't know if this will work in MySQL 4.1.x though. You may want to give
> it a try if you have 4.1.x. By the way, I'm not convinced that this is the
> *best* solution using a subquery; it's just the first one I could think of.
> I don't have all day to spend on this ;-)
The following query works for me.
SELECT content, location, version, date
FROM temp t1
WHERE date=(SELECT MAX(t2.date)
FROM temp t2
WHERE t1.location = t2.location
AND t1.content = t2.content)
AND content = 'ALPHA';
+---------+----------+---------+------------+
| content | location | version | date |
+---------+----------+---------+------------+
| ALPHA | PARIS | 10 | 2004-09-16 |
| ALPHA | NEW-YORK | 10 | 2004-09-16 |
| ALPHA | TOKYO | 11 | 2004-09-15 |
+---------+----------+---------+------------+
3 rows in set (0.01 sec)
Again, that's an adaptation of the subquery version of the solution in the
manual.
Michael
