Articles.FreeMegaZone - Top-N Queries for Oracle

The task of retrieving the top or bottom N rows from a database table is often referred to as a "top-N query" This task is fairly common in application development.

The most simple, but inefficient, way to achieve this task is to retrieve all rows from the database table(s), sort them by specified criteria, scanning from the top, and then selecting the top N rows.

Another option is to write a procedure that retrieves and sorts all rows from a database table. This solution requires lots of code, and most likely it won't execute as quickly as you'd be expecting.

Use a single SQL statement to perform a top-N query. You can do so either by using the ROWNUM pseudo-column available in several versions of Oracle or by utilizing new analytic functions available in Oracle 8i: RANK() and DENSE_RANK().

Consider a table EMP which belongs to the SCOTT schema of the Oracle starter database provided with Oracle database server versions 7.3, 8.0, and 8i. The table structure is:

Let's start out by updating employee James (Empno = 7900) and setting his salary to NULL:

One way to solve this problem is by using the Oracle pseudocolumn ROWNUM. ROWNUM is available in Oracle versions 7, 8.0 and 8i (8.1); I am not sure about Oracle 6. For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

You can use the ROWNUM pseudocolumn to limit the number of rows returned by a query to 5:

If an ORDER BY clause follows ROWNUM in the same query, the rows will be reordered by the ORDER BY clause.

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM Emp
WHERE ROWNUM < 6
ORDER BY Sal;

Because the ROWNUM is assigned upon retrieval, it is assigned prior to any sorting! This is opposite to the result you would get in SQL Server using the TOP clause. In order to select employees with the highest five salaries, you have to force sorting and then apply ROWNUM condition. Here is the syntax for a top-N query where N = 5 (this syntax with the subquery works only in Oracle 8i):

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM Emp
ORDER BY NVL(Sal, 0) DESC)
WHERE ROWNUM < 6;

Oracle - Analytic Functions:

Another way to perform a top-N query uses the new Oracle 8i feature called "analytic functions" The SQL language, while extremely capable in many areas, has never provided strong support for analytic tasks. These tasks require extensive PL/SQL programming, often with performance issues. Oracle 8i now provides a new wide set of analytic functions that address this need.

Ranking Functions:

For a top-N query you can use two ranking functions: RANK and DENSE_RANK. Both allow you to rank items in a group.

RANK() vs. DENSE_RANK():

The difference between RANK() and DENSE_RANK() is that RANK() leaves gaps in the ranking sequence when there are ties. For example Scott and Ford tie for second place with a $3,000 salary; Jones' $2,975 salary brings him in third place using DENSE_RANK() but only fourth place using RANK():

SELECT Empno, Ename, Job, Mgr, Sal,
RANK() OVER (ORDER BY SAL Desc NULLS LAST) AS Rank,
DENSE_RANK() OVER (ORDER BY SAL Desc NULLS LAST) AS Drank
FROM Emp ORDER BY SAL Desc NULLS LAST;

NULLS FIRST | NULLS LAST Clause:

The NULLS FIRST | NULLS LAST clause determines the position of rows with NULL values in the ordered query.

Ordering Sequence:

If the ordering sequence is ascending (ASC), then rows with NULLs will appear last; if the sequence is descending (DESC), then rows with NULLs will appear first. NULLs are considered equal to other NULLs and, therefore, the order in which rows with NULLs are presented is nondeterministic.

Top-N Query:Using RANK()

To obtain a top-N query, use RANK() in a subquery and then apply a filter condition outside the subquery:

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
RANK() OVER
(ORDER BY SAL Desc NULLS LAST) AS Emp_Rank
FROM Emp
ORDER BY SAL Desc NULLS LAST)
WHERE Emp_Rank < 6;

Bottom-N Query:Using RANK()

Using the same technique, you can retrieve the bottom-five employees by salary:

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
RANK() OVER
(ORDER BY SAL ASC NULLS FIRST) AS Emp_Rank
FROM Emp
ORDER BY SAL ASC NULLS FIRST)
WHERE Emp_Rank < 6;

Ranking Functions for Groups:

Ranking functions can be used to operate within groups, too—that is, the rank value gets reset whenever the group changes. This is achieved with a PARTION BY sub clause. Here is the syntax to retrieve the top employee by salary per manager group:

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
RANK() OVER (PARTITION BY MGR ORDER BY MGR, SAL DESC NULLS LAST) AS Emp_Rank
FROM Emp ORDER BY MGR, SAL DESC NULLS LAST) WHERE Emp_Rank = 1;

Conclusion:

As you can see, analytic functions are extremely useful in all types of analysis and computations, and they provide substantial SQL extensions to Oracle 8i.

More Database Articles
   Database Security: Step by step guideline
   Performance Gains by Managing Space: Segments, Tablespace & Datafiles!!
   Writing Portable SQL - A Guideline
   Secrets of Resources with AWR Reports, Great!!
   Why Stored Procedures?