List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:August 10 1999 6:36pm
Subject:Re: Request for SELECT statement help
View as plain text  
Greg Coladonato wrote:
> 
>  > Hi Jules
>  >
>  > The second one doesn't work!
>  > This is, because the Grouping is done on the resultset of the WHERE
>  > part.  So there will be the first t2.else which is found and not
>  > the one belonging to the MAX(..) part.
>  >
>  > So the only way to do this is:
>  > 1) generate temp table:
>  > CREATE tempTable (id int, date DATE);
>  > 2) fill it from the original table:
>  > INSERT INTO tempTable SELECT id, MAX(date) FROM origTable WHERE
>  >    id='such-and-such' GROUP BY id;
>  > 3) get the desired data:
>  > SELECT o.id, o.date, o.else FROM origTable AS o, tempTable AS t
>  > WHERE o.id = t.id AND o.date = t.date;
>  > 4) garbage collect:
>  > DROP tempTable;
>  >
>  > Tschau
>  > Christian
> 
> There's one more wrinkle: if there is more than one row in the
> original table for a particular id and max(date), then you'll need to
> apply an aggregate function to the 'else' column as well. That is,
> 
>  > SELECT o.id, o.date, o.else FROM origTable AS o, tempTable AS t
>  > WHERE o.id = t.id AND o.date = t.date;
> 
> might return more rows than are in tempTable if (id,date) is not
> unique in origTable.
> 
> Greg

Hi Greg

You are absolutely right here, but if you just need any of the possible values, then you
can use another "GROUP BY o.id, o.date" in SELECT 3)

Tschau
Christian

Thread
Request for SELECT statement helpMatt Loschert9 Aug
  • Re: Request for SELECT statement helpJules Bean9 Aug
  • Re: Request for SELECT statement helpChristian Mack9 Aug
  • Re: Request for SELECT statement helpJules Bean9 Aug
    • Re: Request for SELECT statement helpMichael Widenius17 Aug
Re: Request for SELECT statement helpGreg Coladonato10 Aug
  • Re: Request for SELECT statement helpChristian Mack10 Aug