Re: Query with group by

Michael Stassen Fri, 17 Sep 2004 17:14:18 -0700

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query with group by

Reply via email to