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 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;