0

I have two tables.

abc(CID(pk), cname,)
order(order_id(pk), CID(fk), number_of_rentals)

I want to determine top 10 customers on the basis of number of movies they rented. 

select 
    orders.cid,orders.no_rentals, abc.name, 
    rank() over (order by no_rentals desc) "rank" 
from abc 
inner join orders on orders.CID = abc.CID;

I used this query but it's not universal. How can I use sum function on number_of_rentals with this query?

Blorgbeard
  • 101,031
  • 48
  • 228
  • 272
dhruv kadia
  • 45
  • 1
  • 3
  • 10
  • Possible duplicate of [Oracle SELECT TOP 10 records](http://stackoverflow.com/questions/2498035/oracle-select-top-10-records) – MT0 Feb 28 '16 at 23:54

1 Answers1

0
Select Top 10 
         orders.cid
       , abc.name
       , SUM(orders.no_rentals) TotalRentals
       , rank() over (order by SUM(orders.no_rentals) desc) [rank] 
from abc 
inner join orders on orders.CID = abc.CID
Group By orders.cid, abc.name
Order By TotalRentals DESC
M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • SELECT cid, sum(no_rentals) as sum FROM orders group by cid, no_rentals order by no_rentals desc ; this query also gives result but how can i fetch top 10 rows using this query.. i am using oracle sql developer – dhruv kadia Feb 28 '16 at 22:05
  • your query gives error from keyword not found where expected – dhruv kadia Feb 28 '16 at 22:26