Top-N SQL queries

Top-N SQL queries

These type of queries force the ROWNUM condition in a SQL statement to be applied after ordering of the result set. This is achieved by creating a SQL query that contains the ROWNUM condition and a subquery that contains an ORDER BY clause. Top-N SQL queries are more efficient and faster because Oracle avoids sorting all of the rows in the table at once. A feature of Top-N SQL queries is this enhanced sorting mechanism.
The following query will return the 20 smallest check numbers:
SELECT * FROM
(SELECT check_number FROM check_reg ORDER BY check_number)
WHERE rownum < 21 ;
An in-line view is a feature of a Top-N SQL query. It is a subquery that differs from a regular subquery because it contains an ORDER BY. An ORDER BY clause is not allowed in a regular subquery.