Select top rows from a sqlplus query

Question

I have a query

select sum(pur.purchase_net_invoice_value), par.party_desc  from purchase pur
join party par
on par.party_id = pur.party_id
group by par.party_desc;

which runs fine.

I just want to have top five rows of the query and discard the others.

Answer 1

You can use the rownum pseduocolumn for this:

SELECT * 
FROM (SELECT   SUM(pur.purchase_net_invoice_value), par.party_desc
      FROM     purchase pur
      JOIN     party par ON par.party_id = pur.party_id
      GROUP BY par.party_desc
      ORDER BY 1 DESC)
WHERE rownum <= 5;