I took a closer look and found the problem -- char(date). Is char()
necessary in DB2 to cast the date as a string? In MySQL, char() expects a
list of integers to interpret as a list of character codes
<http://dev.mysql.com/doc/mysql/en/String_functions.html>. Removing char()
makes the query appear to work.
I say appear because you forgot the same thing I did. We need to guard
against the possibility that a row with different content will match the
location and date. So, your query becomes
SELECT content, location, version, date
FROM temp
WHERE CONCAT(location, date) IN
(SELECT CONCAT(location, MAX(date))
FROM temp
WHERE content = 'ALPHA'
GROUP BY location)
AND content = 'ALPHA';
which works for me in 4.1.4a.
That said, it seems to me that this is a version of the MAX-CONCAT trick
written as a subquery. With the columns to be compared inside CONCAT()
functions, there is no way to use an index to match up the rows. My
expectation is that this will be relatively inefficient compared to the
other subquery solution.
Michael
Rhino wrote:
> 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
