How to get the first N rows with Oracle

Problem:

You want to get the first ten rows of the result set in Oracle.

Solution:

First 5 rows using classic Top-N style query:

SELECT CUSTOMER_ID, CUST_FIRST_NAME FROM   (SELECT CUSTOMER_ID, CUST_FIRST_NAME         FROM  SOE.CUSTOMERS         ORDER BY CUSTOMER_ID) WHERE ROWNUM < 11; CUSTOMER_ID CUST_FIRST ----------- ----------          1 nicolas          2 fernando          3 bennie          4 dustin          5 perry          6 bruce          7 irving          8 mack          9 douglas         10 ray 10 rows selected.

First 5 rows in Oracle 12c and onward.

SELECT CUSTOMER_ID, CUST_FIRST_NAME FROM SOE.CUSTOMERS ORDER BY CUSTOMER_ID FETCH FIRST 10 ROWS ONLY;   CUSTOMER_ID CUST_FIRST ----------- ----------          1 nicolas          2 fernando          3 bennie          4 dustin          5 perry          6 bruce          7 irving          8 mack          9 douglas         10 ray 10 rows selected.

Note: The FETCH FIRST clause limits the number of rows that a query returns, significantly reducing the SQL complexity of common “Top-N” queries.

I hope this helps.

Please feel free to leave your questions or suggest improvements to this section.