Databases Reference
In-Depth Information
3.
The correct way to express the previously selected statement is:
SELECT * FROM (
SELECT CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME,
CUST_YEAR_OF_BIRTH
FROM CUSTOMERS
ORDER BY CUST_YEAR_OF_BIRTH DESC
)
WHERE ROWNUM < 11;
4.
Using the
RANK()
function may lead to different results:
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;
5.
The
DENSE_RANK()
function is different (again):
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;
6.
Activate the explain plan option:
SET AUTOT TRACE EXP STAT
7.
Execute the query in step 3:
SELECT * FROM (
SELECT CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME,
CUST_YEAR_OF_BIRTH
FROM CUSTOMERS
ORDER BY CUST_YEAR_OF_BIRTH DESC
)
WHERE ROWNUM < 11;
