Oracle is a powerful and flexible relational database management system. One common task when working with Oracle databases is the need to retrieve the top N records from a table. In this article, we will discuss how to do this in Oracle using several different methods, including the use of the SELECT statement, the ROWNUM keyword, and the TOP-N query feature.
Method 1: Using the SELECT Statement
The most basic way to retrieve the top N records from a table in Oracle is to use the SELECT statement. The syntax is as follows:
SELECT column1, column2, ..., columnN
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ..., columnN [ASC|DESC]
FETCH FIRST N ROWS ONLY;
The SELECT statement retrieves the specified columns from the table, and the ORDER BY clause sorts the records based on one or more columns. The FETCH FIRST N ROWS ONLY clause limits the number of rows returned to N.
For example, to retrieve the top 10 employees with the highest salary from the employees table, you would use the following query:
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;
Method 2: Using the ROWNUM Keyword
Another way to retrieve the top N records from a table in Oracle is to use the ROWNUM keyword. ROWNUM is a pseudo column that assigns a unique number to each row in a result set, starting from 1. The syntax is as follows:
SELECT column1, column2, ..., columnN
FROM (
SELECT column1, column2, ..., columnN, ROWNUM as row_num
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ..., columnN [ASC|DESC]
)
WHERE row_num <= N;
The inner SELECT statement retrieves the specified columns from the table and assigns a unique number to each row using the ROWNUM keyword. The outer SELECT statement filters the result set to include only the rows where the row number is less than or equal to N.
For example, to retrieve the top 10 employees with the highest salary from the employees table, you would use the following query:
SELECT first_name, last_name, salary
FROM (
SELECT first_name, last_name, salary, ROWNUM as row_num
FROM employees
ORDER BY salary DESC
)
WHERE row_num <= 10;
Method 3: Using the TOP-N Query Feature
A third way to retrieve the top N records from a table in Oracle is to use the TOP-N query feature. This feature is available in Oracle 12c and later versions. The syntax is as follows:
SELECT column1, column2, ..., columnN
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ..., columnN [ASC|DESC]
FETCH FIRST N ROWS WITH TIES;
The TOP-N query feature is similar to the FETCH FIRST N ROWS ONLY clause, but it also returns additional rows with the same values as the Nth row.
For example, to retrieve the top 10 employees with the highest salary from the employees table
Method 4: Using the RANK() Function
Another way to retrieve the top N records from a table in Oracle is to use the RANK() function. The RANK() function assigns a unique rank to each row in a result set based on the values in one or more columns. The syntax is as follows:
SELECT column1, column2, ..., columnN, RANK() OVER (ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ..., columnN [ASC|DESC]) as rank
FROM table_name
The RANK() function is used in the SELECT statement to assign a unique rank to each row based on the values in one or more columns. The OVER clause is used to specify the columns to be used for ranking. The result set can then be filtered to include only the rows with a rank less than or equal to N.
For example, to retrieve the top 10 employees with the highest salary from the employees table, you would use the following query:
SELECT first_name, last_name, salary, RANK() OVER (ORDER BY salary DESC) as rank
FROM employees
WHERE RANK() <= 10;
Method 5: Using the DENSE_RANK() Function
Another similar function is DENSE_RANK() function, which is also used to assign a unique rank to each row in a result set based on the values in one or more columns, but unlike RANK() it does not leave gaps in the ranking, meaning that if there are multiple rows with the same values, they will all be assigned the same rank.
SELECT column1, column2, ..., columnN, DENSE_RANK() OVER (ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ..., columnN [ASC|DESC]) as rank
FROM table_name
For example, to retrieve the top 10 employees with the highest salary from the employees table, you would use the following query:
SELECT first_name, last_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rank
FROM employees
WHERE DENSE_RANK() <= 10;
In conclusion, Oracle provides various ways to retrieve top N records from a table. The choice of method depends on the specific requirements and the version of Oracle being used. The SELECT statement, ROWNUM keyword, TOP-N query feature, RANK() and DENSE_RANK() functions are all viable options for retrieving top N records in an Oracle database. Each method has its own advantages and disadvantages, and it is important to consider these when choosing the appropriate method for a specific task.
Popular questions
- What is the most basic way to retrieve the top N records from a table in Oracle?
- The most basic way to retrieve the top N records from a table in Oracle is to use the SELECT statement with the FETCH FIRST N ROWS ONLY clause.
- How does the ROWNUM keyword work when retrieving the top N records from a table in Oracle?
- The ROWNUM keyword assigns a unique number to each row in a result set, starting from 1. By using a subquery with the ROWNUM keyword, a unique number is assigned to each row, and then the outer query is used to filter the results to include only the rows where the row number is less than or equal to N.
- What is the TOP-N query feature and how is it used to retrieve the top N records from a table in Oracle?
- The TOP-N query feature is available in Oracle 12c and later versions, it is similar to the FETCH FIRST N ROWS ONLY clause, but it also returns additional rows with the same values as the Nth row.
- How can RANK() function be used to retrieve the top N records from a table in Oracle?
- The RANK() function assigns a unique rank to each row in a result set based on the values in one or more columns, the OVER clause is used to specify the columns to be used for ranking. The result set can then be filtered to include only the rows with a rank less than or equal to N.
- How does the DENSE_RANK() function differ from RANK() function when it comes to retrieving the top N records from a table in Oracle?
- DENSE_RANK() function is similar to RANK() function, but it does not leave gaps in the ranking. If there are multiple rows with the same values, they will all be assigned the same rank.
Tag
Retrieval
