Mastering Oracle SQL

Subquery Case Study: The Top N Performers

Certain queries that are easily described in English have traditionally been difficult to formulate in SQL. One common example is the “Find the top five salespeople” query. The complexity stems from the fact that data from a table must first be aggregated, and then the aggregated values must be sorted and compared to one another in order to identify the top or bottom performers. In this section, you will see how subqueries may be used to answer such questions. At the end of the section, we introduce ranking functions, a new feature of Oracle SQL that was specifically designed for these types of queries.

A Look at the Data

Consider the problem of finding the top five sales people. Let’s assume that we are basing our evaluation on the amount of revenue each salesperson brought in during the previous year. Our first task, then, would be to sum the dollar amount of all orders booked by each saleperson during the year in question. The following query does this for the year 2001:

               SELECT e.lname employee, SUM(co.sale_price) total_sales
               FROM cust_order co, employee e
               WHERE co.order_dt >= TO_DATE('01-JAN-2001','DD-MON-YYYY')
                 AND co.order_dt < TO_DATE('01-JAN-2002','DD-MON-YYYY')
                 AND co.ship_dt IS NOT NULL AND co.cancelled_dt IS NULL
                 AND co.sales_emp_id = e.emp_id
               GROUP BY e.lname
               ORDER BY 2 DESC;

EMPLOYEE             TOTAL_SALES
-------------------- -----------
Blake                    1927580
Houseman                 1814327
Russell                  1784596
Boorman                  1768813
Isaacs                   1761814
McGowan                  1761814 ...