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

SELECT People.FamilyName, BirthDate, Option, CreationDate FROM PEOPLE, ITEM
WHERE People.FamilyName = Item.FamilyName and Item.Option is not null
/

--
Have a nice day
Michel

Lassie <lameto_at_altavista.net> a écrit dans le message : 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
>
Received on Mon Mar 06 2000 - 09:36:41 CST