Retrieve Top N records from a query in Oracle

To retrieve the Top N records from a query, you can use the following syntax:

SELECT *

FROM (your ordered query) alias_name

WHERE rownum <= Rows_to_return

ORDER BY rownum;

For example, if you wanted to retrieve the first 3 records from the suppliers table, sorted by supplier_name in ascending order, you would run the following query:

SELECT *

FROM (select * from suppliers ORDER BY supplier_name) suppliers2

WHERE rownum <= 3

ORDER BY rownum;

If you wanted to retrieve the first 3 records from the suppliers table, sorted by supplier_name in descending order, you would run the following query:

SELECT *

FROM (select * from suppliers ORDER BY supplier_name DESC) suppliers2

WHERE rownum <= 3

ORDER BY rownum;

If you wanted to retrieve the first 5 records from the suppliers table, sorted by supplier_id in ascending order, you would run the following query:

SELECT *

FROM (select * from suppliers ORDER BY supplier_id) suppliers2

WHERE rownum <= 5

ORDER BY rownum;

If you wanted to retrieve the first 5 records from the suppliers table, sorted by supplier_id in descending order, you would run the following query:

SELECT *

FROM (select * from suppliers ORDER BY supplier_id DESC) suppliers2

WHERE rownum <= 5

ORDER BY rownum;