Re: SQL Query Question

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