Micksnothere 0 Newbie Poster

Hello,

I am trying to write an sql query in oracle that solves this question:

List top three customers from each state that has the most number of orders (4 points).

Here is my query now that is just ranking every customer based on the # of orders they have placed. How can I now subdivide that based on state?

select c.state, c.fname, count(o.customer_no) as order_count,
dense_rank() over (order by count(o.customer_no) desc)as rank
from oes2.customer c
inner join oes2.orders o on c.customer_no = o.customer_no
group by c.state, c.customer_no, c.fname

I have come across some examples online but none of them are using a join.

Thanks,

Mick