The Oracle (tm) Users' Co-Operative FAQ

How do I report the top 10 members of the sales team?


Author's name: Niall Litchfield  

Author's Email: n-litchfield@audit-commission.gov.uk

Date written: 25/06/01

Oracle version(s): 8.1.7

How do I select the top performing members of the sales team, for example to give them a bonus. Or more generally how to select the top (or bottom) n records from a table based on a particular column

Back to index of questions


This question most typically arises for folks who come from a SQL Server background who have long had the ability to issue a command such as

SELECT TOP 5 qty, title_id FROM sales
to select the top 5 books from a table containing sales volumes. The TOP keyword is not supported in Oracle and therefore issuing such a query will result in the error
ERROR at line 1: 
ORA-00923: FROM keyword not found where expected

The most usual recommendation given is to use the pseudo-column rownum to restrict the output. Our query would then become

SELECT qty,title_id FROM sales WHERE rownum < 6 ORDER BY qty DESC;

This recommendation is highly misleading as a glance at the explain plan will show:

Execution Plan 
---------------------------------------------------------- 
0 SELECT STATEMENT Optimizer=CHOOSE 
1 0 SORT (ORDER BY) 
2 1  COUNT (STOPKEY) 
3 2   TABLE ACCESS (FULL) OF 'SALES'

The plan shows that Oracle carries out an unordered scan of the table. It then takes the first 5 of the unordered resultset and orders them by the requested column. Thus it will not necessarily return the top 5 records. This sort of error becomes somewhat important when the information is used, for example, to reward top performers or conversely to discipline or fire poor performers.

The Oracle Solution (8.1.5 and above).

With the introduction of Oracle 8i comes the ability to order inline views. Thus for 8i we can issue

SELECT qty,title_id FROM
( SELECT qty,title_id FROM sales ORDER BY qty)
WHERE rownum < 6;

Here our execution plan becomes

Execution Plan
----------------------------------------------------------
0   SELECT STATEMENT Optimizer=CHOOSE
1 0   COUNT (STOPKEY)
2 1     VIEW
3 2       SORT (ORDER BY STOPKEY)
4 3         TABLE ACCESS (FULL) OF 'SALES'

In other words the sort is done before the restriction of output. This method is guaranteed to work in all versions of Oracle 8i. It is also possible that it will work in late versions of oracle 7.3.4 as it appears that this feature was accidentally back-ported to Oracle 7.3.4.4 (confirmation/refutation of this point would be gratefully accepted)

NB There are in 8.1.6 and greater the analytic functions RANK() and DENSERANK() which give us an alternative solution and one which may perform better in more complex situations. The syntax is however a little convoluted. Our statement using this alternative becomes

SELECT qty, title from
(select qty,title_id,rank() over(order by qty desc) rk FROM SALES)
WHERE rk < 6;

The Oracle Solution (8 & below).

The alternative solution for older versions of Oracle is to use hints to nudge Oracle into returning rows in the correct order before we filter them , the typical hint to use in this case is INDEX_DESC(TABLE_NAME INDEX). So in our example the statement would be

SELECT qty,title_id FROM
(SELECT /*+ INDEX_DESC (SALES SALES_QTY_IDX) */ qty,title_id FROM sales)
WHERE rownum < 6;

Where SALES_QTY_IDX is an index on the quantity column of the sales table.


Further reading: N/A


Back to top

Back to index of questions