SQL Query Question

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. Received on Fri Apr 11 1997 - 00:00:00 CDT