PURPOSE
This note explains the TOP-N Query, a new feature (as of 8.1.5) that allows
users to query on ROWNUM and order results with ORDER BY. This functionality
is similar to the offerings of other database software such as the “Top
Values’ property option in Microsoft Access.
Top-N Query to Solve ORDER BY…ROWNUM Inaccuracy:
==================================================
What is a Top-N Query?
———————-
Top-N queries use a consistent nested query structure to show the largest or
smallest values of a column. For example, what are the 10 best selling products,
what are the 10 worst selling products?
Top-N queries are useful in scenarios where we need to find the top-n or
bottom-n records from a table based on a condition.
The syntax for a Top-N query is:
SELECT [column_list], ROWNUM
FROM (SELECT [column_list] FROM table
ORDER BY Top-N_column)
WHERE ROWNUM <= N;
A subquery or an inline view generates the list of sorted data. The subquery
includes the ORDER BY clause to ensure that the ranking is in the desired
order. The outer query is used to limit the number of rows in the final result
set. The ROWNUM pseudo-column assigns a sequential value starting with 1 to
each of the rows returned from the subquery. A WHERE clause with ROWNUM
specifies the n rows to be returned.
‘Select ROWNUM…ORDER BY’ Returns Correct Results with TOP-N Query:
——————————————————————–
You want to issue a query to return rows based on a specific value. For
example, you want to see the rows for the 10 highest paid employees. You issue
a query similar to:
SQL> select empno, ename, sal
from emp
where rownum <=10
order by sal desc;
However, you know that the results this query returns are not correct.
In 8.1.5.x and greater you can restructure the query as follows:
SQL> select empno, ename, sal
from
(select empno, ename, sal
from emp
order by sal desc)
where rownum <=10;
This is a TOP-N Query. Structuring the query in this manner allows Oracle to
use SQL operations in the proper order to return the proper results. The nested
query first selects ALL rows from the table and sorts them in descending order
of the sal column.