Oracle Select Top Syntax

The "select top" syntax is a syntax used by certain databases to limit the returned rows to the first N rows of a query.

Oracle does have a way to select the top N rows from an SQL query, but the syntax does not use the top keyword. Instead, the ROWNUM keyword must be used.

For example, databases that support the top syntax may use the following syntax to return the first 50 rows from a query:

select top 50 * from sales

The Oracle equivalent of the above syntax would be the following:

select * from sales where rownum <= 50

When using the Oracle ROWNUM, the rows may not be returned in the specific order desired by the user. To work around this limitation, a ROWNUM query can be wrapped around a query with an order by clause. Here is an example:

select * from (select * from sales order by revenue desc) where rownum <= 50