Retrieve Only First x Rows

From time to time you may have the need to grab the top X number of rows returned from a table. For instance “give me the 10 vendors we pay the most from the voucher table”.

This might seem impossible in something like query as there is no way to stop the output after 10 rows… unless you use the SQL below that will let you do just that:

SELECT *FROM (SELECT VENDOR_ID, COUNT(*) FROM PS_VOUCHER GROUP BY VENDOR_ID ORDER BY 2 DESC)WHERE ROWNUM <= 10

 

What this will do is run the inner SQL statement (a view that acts like a table) that will grab vendor counts from the voucher table and order it by the count descending. Then the outer SQL statement will only show the first 10 rows returned.

If you needed a larger number simply increase the 10 to what ever count you need. This particular SQL can be slow since it is doing a full table scan of the voucher table.

The return from this table can also be joined to other tables for a report.  (This example would need more fine-tuning to pull the single row desired from PS_VENDOR_ADDR, but for simplicity the correct SQL has not been added.)

SELECT V.CNT, AD.*FROM PS_VENDOR_ADDR AD, (SELECT VENDOR_ID, COUNT(*) AS CNT FROM PS_VOUCHER GROUP BY VENDOR_ID ORDER BY 2 DESC) VWHERE ROWNUM <= 10 AND AD.VENDOR_ID = V.VENDOR_ID