>From the SQL Reference for DB2:
---
>>-CHAR--(--datetime-expression----+---------------+--)--------><
'-,--+-ISO---+--'
+-USA---+
+-EUR---+
+-JIS---+
'-LOCAL-'
Datetime to Character
datetime-expression
An expression that is one of the following three data types
date
The result is the character string representation of the date in the
format specified by the second argument. The length of the result is 10. An
error occurs if the second argument is specified and is not a valid value
(SQLSTATE 42703).
time
The result is the character string representation of the time in the
format specified by the second argument. The length of the result is 8. An
error occurs if the second argument is specified and is not a valid value
(SQLSTATE 42703).
timestamp
The second argument is not applicable and must not be specified.
SQLSTATE 42815 The result is the character string representation of the
timestamp. The length of the result is 26.
The code page of the string is the code page of the database at the
application server.
---
So, yes, char() is converting the date to a string as you suspected.
I'm not suprised that the query wouldn't perform terribly well; that's what
I would have expected given the construction of the query. Like I said, it
was just the first solution I came up with. I would likely have come up with
something more like your solution if I had had more time to try other
solutions :-)
In any case, we have now thought of several solutions to the problem. I hope
Vincent can use at least one of them ;-)
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 8:13 PM
Subject: Re: Query with group by
> 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]
