Extract Max amount's reccord from each customer

[rizhaque]

Need SQL that could pull only one record per customer with the highest amount.

Here is the table:

Last First Gift Date MaxAmount
James kim 17-Dec-02 $90.00 This record
James kim 23-Jan-96 $10.00
James kim 12-Feb-97 $9.00
weeks alen 01-May-99 $33,000.00 This record
weeks alen 02-Feb-92 $30,000.00
weeks alen 31-Dec-91 $27,000.00
Finkelstine Gail 25-Apr-89 $16,000.00 This record
Finkelstine Gail 27-Dec-96 $13,000.00
Finkelstine Gail 01-Apr-91 $12,000.00

Thanks,

 

[Russell Hauf]

Ok, then try this:

• SELECT A.*, B.[GiftDate] FROM (Select LastName, FirstName, Max(MaxAmount) AS MaxAmt
  FROM Test
  GROUP BY LastName, FirstName) AS A, Test B
  WHERE A.LastName=B.LastName AND A.FirstName=B.FirstName and A.MaxAmt=B.MaxAmount;

Let me know if this one works.

-Russell

 

[rizhaque]

thanks so much...
worked well....