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)

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

Top Next