Here is the output I got when I ran my query - and yours, Michael - against DB2 V7.2. Please note that I replaced the 'temp' table in Michael's query with the real table in both the outer query and the subquery; no other changes were made.
------------------------------------------------------------------ create table versions (id smallint not null, date date not null, content char(5) not null, location char(10) not null, version smallint not null, primary key(id)) DB20000I The SQL command completed successfully. insert into versions values (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) DB20000I The SQL command completed successfully. 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) CONTENT LOCATION VERSION DATE ------- ---------- ------- ---------- ALPHA NEW-YORK 10 09/16/2004 ALPHA PARIS 10 09/16/2004 ALPHA TOKYO 11 09/15/2004 3 record(s) selected. SELECT content, location, version, date FROM versions t1 WHERE date=(SELECT MAX(t2.date) FROM versions t2 WHERE t1.location = t2.location AND t1.content = t2.content) AND content = 'ALPHA' CONTENT LOCATION VERSION DATE ------- ---------- ------- ---------- ALPHA PARIS 10 09/16/2004 ALPHA NEW-YORK 10 09/16/2004 ALPHA TOKYO 11 09/15/2004 3 record(s) selected. ------------------------------------------------------------------ As you can see, both queries worked and produced the same result in DB2, aside from the row sequence, which is easily fixable via an Order By. I'm at a loss to explain why my query didn't work in MySQL V4.1.4. Rhino ----- Original Message ----- From: "Michael Stassen" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Cc: "Jose Miguel Pérez" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, September 17, 2004 12:42 AM Subject: Re: Query with group by > > 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]
