How do I report the top 10 product members of the sales team in each state/county ?

The Oracle (tm) Users' Co-Operative FAQ

How do I report the top 10 product members of the sales team in each state/county ?

Back to index of questions

Suppose you table is something like this

Table Sales_Data
Column 1:  . State
Column 2:  . Member_id
Column 3:  . Criterion (Where criterion is something you use to define your top ten. (like no.of units sold etc.))

There are two ways to write this query. Using functions that have been made available in the 8i version or doing it without them. Of course, the pre 8i version is good for 8i too. But why slog when somebody has done the work for you already!

Pre 8i

select state, memberid, criterion
from (select rownum - (select count(*) from sales_data where state < a.state) as member_rank, criterion, state, memberid
                   from (select memberid, criterion, state
                             from sales_data
                             order by state,criterion desc ) a) b
where b.member_rank < 11;

OR Post 8i,

You can use the rank function

Select a.state, a.member_id,a.criterion
          from (select member_id, state ,criterion,rank() over(partition by state order by criterion desc) as member_rank
                     from sales_data ) a
where a.member_rank < 11
order by a.state, a.member_rank

Further Reading: n/a