Re: any way to get MAX?

Totti wrote:
>
>
> 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, 'MM-
> YY')
> 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

Go to this page:
http://www.psoug.org/reference/analytic_functions.html

And look at the following functions:
DENSE_RANK
MAX
RANK

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org