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;

Answer 2

You can use the ROWNUM Pseudocolumn to limit the number of rows.
Add an ORDER BY clause in the sub-query to define which rows to show.

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
)
WHERE ROWNUM <= 5;