Re: SQL Query Question

Joost,

   The reason the original version did not work was because the subquery has to separately evaluate the maximum salary for a paycode for *every* row in the outer-query join (from which it gets the value of the paycode). This outer query requires a full-table scan of at least one of the tables (probably the PEOPLE table: I bet PAYAMOUNT is not indexed!) and an indexed access to each row of the other table. This could clearly take a very long time and, possibly, might not complete at all if you had too small a temporary storage area.

   The second version does one pass of PAYMENTS to determine *all* the maxima (one for each paycode) and then uses the results of this (presumably) small set to "drive" the outer query.

   Some people think that subqueries always "drive" the outer query. This example shows that this is not so.

Chrysalis.

Joost Ouwerkerk wrote:
>
> On Sat, 12 Apr 1997 01:08:31 +0000, Chrysalis <cellis_at_iol.ie> wrote:
>
> Well, it worked -- THANK YOU. Now, can you explain why it didn't
> work when I was driving the original query. I can see why your query
> works (and indeed, I am sometimes getting more than one payment that
> is equal to the MAX -- but that is okay.) But why didn't mine?
>
> Joost Ouwerkerk.
>
> >Joost Ouwerkerk wrote:
> >>
> >> This probably has a simple answer and this may not even be the
> >> appropriate forum for SQL questions, but here goes:
> >>
> >> Two tables -- one contains PAYMENTS and one PEOPLE. I want the top
> >> payments for each account code. This is easily done with a
> >> MAX(PAYAMOUNT) and a GROUP BY CODE. Now, how do I identify the person
> >> associated with each of these maximum payments. Obviously, MAX (NAME)
> >> will not work. The tables are related by IDNUMBER.
> >>
> >> I've tried using a subquery along the lines of:
> >>
> >> SELECT pay.CODE, pay.PAYAMOUNT, pe.IDNUMBER, pe.NAME
> >> from PAYMENTS pay, PEOPLE pe
> >> where pay.IDNUMBER=pe.IDNUMBER
> >> and pay.PAYAMOUNT = ( SELECT max (pa2.PAYAMOUNT) from
> >> PAYMENTS pa2 where pa2.CODE=pay.CODE)
> >> ORDER by CODE;
> >>
> >> It doesn't work -- i.e. it keeps working ad infinitum.
> >>
> >> Any suggestions? I've run into this problem many times before -- how
> >> do I get columns associated with a MAX column of the same row?
> >>
> >> (PS Column and table names have been changed to protect the simplicity
> >> of this question)
> >>
> >> Joost Ouwerkerk
> >> Development Analyst
> >> HSC Foundation
> >> Toronto, Ontario.
> >
> >Since you want the results for *all* values of pay.CODE, try:
> > SELECT pay.CODE, pay.PAYAMOUNT, pe.IDNUMBER, pe.NAME
> > from PAYMENTS pay, PEOPLE pe
> > where pay.IDNUMBER = pe.IDNUMBER
> > and (pay.code, pay.PAYAMOUNT) in
> > (SELECT pa2.code,max (pa2.PAYAMOUNT)
> > from PAYMENTS pa2
> > group by pa2.CODE)
> >ORDER by pay.CODE;
> >
> >The subquery returns the maximum for each group and is used to "drive"
> >the query.
> >(Don't be surprised if more than one person qualifies from each group!)
> >
> >Hope this helps.
> >
> >Chrysalis.
Received on Mon Apr 14 1997 - 00:00:00 CDT