any way to get MAX?

From: Totti <saliba.toufic.george_at_gmail.com>
Date: Sun, 6 Jan 2008 11:02:12 -0800 (PST)
Message-ID: <eb4444a5-56ba-4458-93c7-87fd3416703e@d21g2000prf.googlegroups.com>

Hi all , i want to get out of the data, the most popular good/month i have in my data, i figured out that it would be the one having the maximum sales, so i m using the following formula to see for every month, and every product, and how much were the sales this month, now the solution would be if i can get the max amount and it is therefore the product i need, but how could that be done if i have the following?



select (to_char(salinv_1.inv_date, 'MM'))as "MM",
(to_char(salinv_1.inv_date, 'YY')) as "YY",
(sales_1.prod_code) as "Code",

sum(salpmt_1.amount) as "Total Purchase" from sales_1,salinv_1, salpmt_1
where salinv_1.code = sales_1.inv_code and to_char(salpmt_1.pmt_date,'MM-YY') = to_char(salinv_1.inv_date, 'MMYY')
group by (sales_1.prod_code),

(to_char(salinv_1.inv_date, 'MM')),
(to_char(salinv_1.inv_date, 'YY'))

order by (to_char(salinv_1.inv_date,'YY'))
MM YY Code            Total Purchase
--     -- ---------------         --------------
01 05 SP-20.1                 345180
01 05 SP-20.2                 396957
01 05 SP-20.3                 138072
01 05 SP-20.4                 138072
01 05 SP-20.5                 241626
01 05 SP-20.6                 276144
01 05 SP-20.7                 276144
02 05 SP-20.1                 328445
02 05 SP-20.2                 353710
02 05 SP-20.3                 151590
02 05 SP-20.4                 303180
02 05 SP-20.5                 277915
02 05 SP-20.6                 378975
02 05 SP-20.7                 176855
============================================
while what i need is like so :
MM YY Code            Total Purchase
--     -- ---------------         --------------
01 05 SP-20.2                 396957
02 05 SP-20.2                 353710
============================================
is it possible?
and thank you very much Received on Sun Jan 06 2008 - 13:02:12 CST

Original text of this message