Re: Optimize a query : Replacing the Group By Clause ?

Lassie <lameto_at_altavista.net> wrote in message news:38C3C7B6.A934B0A4_at_altavista.net...
> Hi Everyone.
> I have a (newbie ?) question about this example query :
>
> Two tables :
> PEOPLE (FamilyName, BirthDate)
> Name1 | 01/01/2000
> Name2 | 02/02/2000
>
> ITEM (FamilyName, ItemNumber, Option, CreationDate)
> Name1 | #1 | <empty>| 10/10/1999
> Name2 | #1 | <empty> | 11/11/1999
> Name2 | #2 | Option_for_ Name2 | 12/12/1999
>
> This means : Name2 has an Option, and Name1 don't.
> The option can be on #1 OR #2 item number => this means I cannot use
> ItemNumber to select the line from ITEM
>
> QUERY :
> This is the result I want :
> Name1 | 01/01/2000 | <Empty> | 10/10/1999
> Name2 | 02/02/2000 | Option for Name2 | 12/12/1999
>
> This is the Query I am using for now :
>
> SELECT People.FamilyName, BirthDate, MAX(Option), MAX(CreationDate)
> FROM PEOPLE, ITEM
> WHERE People.FamilyName = Item.FamilyName
> GROUP BY People.FamilyName, BirthDate
>
> It's working, but is very slow because of the "group by" clause. In
> addition, this is not very "clean" especially for the
> MAX(CreationDate)....
> Is there another way to get the same result ?
> Thanks for reading this long post !...
>
> Lassie
>

Sorry to say so, but if the option can occur both on option and on itemnumber, that's bad table design.
Hth,

Sybrand Bakker, Oracle DBA Received on Mon Mar 06 2000 - 09:26:40 CST