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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
