List:General Discussion« Previous MessageNext Message »
From:Rhino Date:September 18 2004 3:37am
Subject:Re: Query with group by
View as plain text  
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" <Michael.Stassen@stripped>
To: "Rhino" <rhino1@stripped>
Cc: "Jose Miguel Pérez" <jruiz@stripped>; <mysql@stripped>;
<Vincent.Badier@stripped>
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
>

Thread
Query with group byVincent.Badier16 Sep
  • Re: Query with group byRhino16 Sep
    • Re: Query with group byVincent.Badier16 Sep
  • Re: Query with group byJose Miguel Pérez16 Sep
    • Re: Query with group byMichael Stassen16 Sep
  • Re: Query with group byRhino16 Sep
    • Re: Query with group byMichael Stassen17 Sep
  • Re: Query with group byJose Miguel Pérez16 Sep
    • Re: Query with group byMichael Stassen17 Sep
      • RE: Query with group byJose Miguel Pérez22 Sep
  • Re: Query with group byRhino17 Sep
    • Re: Query with group byMichael Stassen18 Sep
  • Re: Query with group byRhino18 Sep