Utilizing Oracle 8i's Ranking 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, such as computing rankings, cumulative and moving averages, lead/lag comparisons, and reporting. 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.
For a top-N query you can use two ranking functions: RANK and DENSE_RANK. Both allow you to rank items in a groupfor example, finding the top-five employees by salary, which is exactly what we need to achieve.
The difference between RANK() and DENSE_RANK() is that RANK() leaves gaps in the ranking sequence when there are ties. In our case, 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,
(ORDER BY SAL Desc NULLS LAST) AS Rank,
(ORDER BY SAL Desc NULLS LAST) AS Drank
ORDER BY SAL Desc NULLS LAST;
shows the output of the above code.
The NULLS FIRST | NULLS LAST clause determines the position of rows with NULL values in the ordered query.
If the sequence is in descending order, then NULLS LAST implies that NULL values are smaller than non-NULL ones and rows with NULLs will appear at the bottom of the list. If the NULLS FIRST | NULLS LAST clause is omitted, then NULL values are considered larger than any other values and their ordering position depends on the ASC | DESC arguments.
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.