MAX RECORD DISPLAY

[mbfloyd]

Hi, I am trying to display a max record from multiple tables.
example:
select a.cust_name, a.cust_address, a.cust_num, b.cust_amt, c.cust_cat
from a, b, c
where a.cust_num = b.cust_num
GROUP BY b.cust_amt
order by a.cust_name asc

I am getting all records, but I need the max cust_amt record for each customer.

cust_name cust_num cust_amt cust_cat
ADAM 001 20,000 AGP
ADAM 001 19,500 AGP
EVE 002 5,000 AGP
JOHN 003 11,000 AGP
EVE 002 27,000 AGP


RESULTS NEEDED:
cust_name cust_num cust_amt cust_cat
ADAM 001 20,000 AGP
EVE 002 27,000 AGP
JOHN 003 11,000 AGP

ANY HELP WILL BE APPRECIATED
THANKS
MARQ

 

[shils]

--This will work

--=============================================
select a.cust_name, a.cust_num, max(b.cust_amt), c.cust_cat
from a, b, c
where a.cust_num = b.cust_num and b.cust_num = c.cust_num
GROUP BY a.cust_name, a.cust_num, c.cust_cat
order by a.cust_name asc

--=============================================
--Shils Jacob Kochumuttom
--=============================================