One common problem when developing database applications is to show the first n rows of a set, ordering the data in a specific manner. For example, if we want to see the last 10 articles submitted in a web application.
In this recipe, we will see how to obtain this scope and how to obtain it faster.
The following steps will demonstrate how to get the top n queries and their ranking:
Connect to the
SH
schema:CONNECT sh@TESTDB/sh
Select the first 10 customers, ordered by their age, from youngest to oldest:
SELECT CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_YEAR_OF_BIRTH FROM CUSTOMERS WHERE ROWNUM < 11 ORDER BY CUST_YEAR_OF_BIRTH DESC;
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;
Using the
RANK()
function may lead to different results:SELECT * FROM ( SELECT...