Optimizing Sort Operations - Oracle Database 11gR2 Performance Tuning - page 220

8. Execute the query in step 4:

SELECT * FROM (
SELECT CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME,
CUST_YEAR_OF_BIRTH,
RANK() OVER (ORDER BY CUST_YEAR_OF_BIRTH DESC) AS RANKING
FROM CUSTOMERS
)
WHERE RANKING < 11;

9. Execute the query in step 5:

SELECT * FROM (
SELECT CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME,
CUST_YEAR_OF_BIRTH,
DENSE_RANK() OVER (ORDER BY CUST_YEAR_OF_BIRTH DESC) AS RANKING
FROM CUSTOMERS
)
WHERE RANKING < 11;

How it works...

We want to retrieve the 10 youngest customers in our CUSTOMERS table.

The query in step 2 is wrong , because the filter—the WHERE condition—is evaluated before
the ORDER BY clause. The output displayed in this case shows the first 10 rows in the
CUSTOMERS table (retrieved without any particular ordering) ordered in descending order
by the CUST_YEAR_OF_BIRTH field.

You can see the results of this query in the next screenshot: