Re: SQL Query Question

From: Chrysalis <cellis_at_iol.ie>
Date: 1997/04/12
Message-ID: <334EE08C.6AC3@iol.ie>#1/1

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 Sat Apr 12 1997 - 00:00:00 CDT