Top N

To produce a Top N report

1. Enclose ORDER BY in subquery
2. Put ROWNUM condition in top-level query
3. Forces ROWNUM condition evaluation after rows sorted.

To list a baker's top three donut buyers:

SELECT customer,units_ytd FROM
 (SELECT customer,units_ytd 
    FROM customers ORDER BY units_ytd desc)
WHERE ROWNUM < 4;

CUSTOMER                UNITS_YTD
----------------------- ---------
Safeway                   102,853
Nob Hill                   81,964
Albertsons                 75,692