RE: Query with group by

Hi Michael! Talking about the "query with group by issue"... > I'll explain my reasoning below. [...] > From the manual, section "7.2.8 How MySQL Optimizes LEFT > JOIN and RIGHT > JOIN" : > >A LEFT JOIN B join_conditi

Re: Query with group by

ast 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

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 . Removing char() makes the query

Re: Query with group by

Jose Miguel Pérez wrote: Hi Michael! Yes, you're right, thanks for extending and clarifying my message. However, I'm not confident about your comments on the inefficiency for the following query: I'll explain my reasoning below. SELECT c1.date, c1.location, c1.version FROM cities

Re: Query with group by

- 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

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 on

Re: Query with group by

Hi Michael! Yes, you're right, thanks for extending and clarifying my message. However, I'm not confident about your comments on the inefficiency for the following query: > > SELECT c1.date, c1.location, c1.version > > FROM cities c1 > > LEFT JOIN cities c2 > >

Re: Query with group by

>Which version of MySQL are you running? > >I'm having trouble thinking of a solution that doesn't involve a subquery >but subqueries aren't supported until version 4.1.x; I don't want to give >you a subquery if you can't run it. > >Rhino I'm running 3.23.43. -- Vincent -- MySQL General Mai

Re: Query with group by

e - From: "Michael Stassen" <[EMAIL PROTECTED]> To: "Jose Miguel Pérez" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, September 16, 2004 10:47 AM Subject: Re: Query with group by > > Jose Miguel Pérez wrot

Re: Query with group by

Jose Miguel Pérez wrote: Hi Vincent! i'm trying to built a query that report me the latest tuple for a given field. I explain myself a bit. Let's say i have a table such this : There is no simple solution to your problem. Strictly speaking, it's forbidden to name a column in the SELECT clause w

Re: Query with group by

Hi Vincent! > i'm trying to built a query that report me the latest tuple for a given > field. > I explain myself a bit. Let's say i have a table such this : There is no simple solution to your problem. Strictly speaking, it's forbidden to name a column in the SELECT clause which is NOT on th

Re: Query with group by

- Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, September 16, 2004 3:05 AM Subject: Query with group by > Hello all, > > i'm trying to built a query that report me the latest tuple for a given > field. > I explain myself a bit. Let's say i have a t