top 10 rows in oracle

Question

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

Are you sure you want to `SUM` *and* `GROUP BY` the `no_rentals` column? — Dai, Feb 28 '16 at 22:56
@Dai i want to fetch top 10 customers based on number of rentals so i think i have to do that — dhruv kadia, Feb 28 '16 at 23:09
You just need to `GROUP BY` the `cid` column, you don't group by the `no_rentals` column because it's being `SUM`med for each `cid`. I suggest you read-up on how Aggregate Functions work when used with `GROUP BY` statements. — Dai, Feb 28 '16 at 23:11

MT0 · Answer 1 · 2016-02-28T23:35:50.540

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;

top 10 rows in oracle

1 Answers1