Top 10 rankings

[ridhirao22]

Hi All,

I have Division, Styles, units and amount. I need to find the top 10 styles with the most units ordered by each division. I try to do with dense_rank but couldn’t get it right. Any help is appreciated

Thanks
RR

 

[ridhirao22]

Here is what I am trying to do:for ex:
Which seems to give me the top 10 ranks. But I need the top 10 rank for each division:


Select * from (
(select div,style,units,amount,dense_rank() over (order by units desc) topRank
(Select
divisionName div,
style_code style,
sum(units) units,
sum(amount) amount
from
orders a,
styles b,
division c
where a.ord_id = b.ord_id
and b.level_1id = c.divi_id
group by divisionName,
style_code) a) b
where topRank <= 10

 

[LKBrwnDBA]

Try this:

SELECT *
FROM ((SELECT div,
              style,
              units,
              amount,
              ROW_NUMBER( ) OVER (PARTITION BY div ORDER BY units DESC) toprank
       FROM (SELECT divisionname div,
                    style_code style,
                    SUM( units ) units,
                    SUM( amount ) amount
             FROM orders a, styles b, division c
             WHERE a.ord_id = b.ord_id AND b.level_1id = c.divi_id
             GROUP BY divisionname, style_code) ))
WHERE toprank <= 10

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Ridhirao,

Try placing the clause "partition by div" in front of your "order by..." clause. (I'd be happy to test this for you, but I don't have time to generate the "CREATE TABLE..." and "INSERT INTO..." statements to run the test.)

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[ridhirao22]

Thank you guys, I realize that I forgot to do Partition by.

Thanks you both for quickly responding, Very much Appreciated!