how to get specific values from multi-table function

From: Totti <saliba.toufic.george_at_gmail.com>
Date: Sat, 12 Jan 2008 16:45:47 -0800 (PST)
Message-ID: <5e399b2c-40d7-4154-a8b2-a6e6a4fe7182@e10g2000prf.googlegroups.com>

hi all,
i am posting a sample of a result i am getting from the following formula

---

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 Sales"
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'))

---

• Results

  ---

  12 05 SP-20.6 453772 12 05 SP-20.7 288764 01 06 SP-20.1 258731 01 06 SP-20.2 282252 01 06 SP-20.6 188168 02 06 SP-20.4 55868 02 06 SP-20.5 97769 03 06 SP-20.1 178960 03 06 SP-20.2 178960

  ---

  the results go so for every product monthly till the end; i need the best product monthly, so this would be the max of sales for each month; meaning 1 prod/month with max sales; can you please tell me how would this be possible?

thanks Received on Sat Jan 12 2008 - 18:45:47 CST