i have 2 tables . abc(CID(pk), cname,) order(order_id(pk), CID(fk), number_of_rentals)
i want to fetch top 10 customers based on number of rentals.
SELECT cid, sum(no_rentals) as sum FROM orders group by cid, no_rentals order by no_rentals desc; how can i use rownum function in above query to fetch the desired output
Just wrap your query in:
SELECT * FROM ( your_query ) WHERE ROWNUM <= 10; However, your query does not look like it is going to do what you intend as the GROUP BY no_renalts will mean that each distinct no_rentals value will be in its own group and you will not sum the values for each customer so you probably don't want to include it in the GROUP BY. Also, if you want to order by the total number of rentals then you want to ORDER BY SUM( no_rentals ) (or by its alias) like this:
SELECT cid, SUM(no_rentals) as total_no_rentals FROM orders GROUP BY cid ORDER BY total_no_rentals DESC; Then you can apply the row limit like this:
SELECT * FROM ( SELECT cid, SUM(no_rentals) as total_no_rentals FROM orders GROUP BY cid ORDER BY total_no_rentals DESC ) WHERE ROWNUM <= 10; 
SUMandGROUP BYtheno_rentalscolumn?GROUP BYthecidcolumn, you don't group by theno_rentalscolumn because it's beingSUMmed for eachcid. I suggest you read-up on how Aggregate Functions work when used withGROUP BYstatements.