Answer: You can limit the number of rows returned by an Oracle query after ordering using FETCH or ROWNUM queries.
Limiting the number of rows returned by an Oracle query after ordering is important because it makes the data work efficiently and reduces load time. In this blog, let’s explore different approaches to limit the number of rows returned by an Oracle query after ordering in detail with an example for each.
Table of Contents:
Methods to Limit the Rows returned by an Oracle Query after Ordering
First, let’s create a dataset to operate on how to limit the number of rows by an Oracle query after ordering and use the same datasets for all the examples of the methods.
Example:
--Creation of the students table:
CREATE TABLE students (
roll_number NUMBER PRIMARY KEY,
name VARCHAR2(100)
);
--Insertion of the values into the table:
INSERT INTO students VALUES (1, 'Arun');
INSERT INTO students VALUES (2, 'Babu');
INSERT INTO students VALUES (3, 'Munna');
INSERT INTO students VALUES (4, 'Kishore');
INSERT INTO students VALUES (5, 'Vimal');
COMMIT;
--Format output for better readability
COLUMN roll_number FORMAT 9999 HEADING 'ROLL NO'
COLUMN name FORMAT A20 HEADING 'STUDENT NAME'
COLUMN marks FORMAT 999 HEADING 'MARKS'
--Display the created students table
SELECT * FROM students;
Output:
This is the table that we have created with the student’s name and roll number.
The Oracle database provides multiple ways to restrict the number of rows in a query result after sorting, ensuring efficient data retrieval.
Method 1: Using the FETCH command in Oracle SQL
The FETCH command will be used to fetch the specified number of rows in a dataset after ordering the data.
Example:
SELECT * FROM students ORDER BY roll_number FETCH FIRST 3 ROWS ONLY;
Output:
Explanation: The Fetch command here displays the first 3 rows limiting the next 2 rows.
Using OFFSET in FETCH
The OFFSET will be used to fetch data by skipping the rows. For example, if we need to print the last 2 rows and delete the first 3 rows, then OFFSET will be used to fetch those data.
Example:
SELECT *
FROM students
ORDER BY roll_number
OFFSET 3 ROWS FETCH NEXT 2 ROWS ONLY;
Output:
Explanation: The OFFSET removes the first 3 rows from the table and displays the next 2 rows after the removal.
Method 2: Using ORDERBY with ROWNUM in Oracle SQL
The Order By in ROWNUM assigns the value to the row before sorting. But doing this will cause errors while retrieving. To overcome this, we can use subqueries like WHERE.
Example:
SELECT *
FROM (SELECT roll_number, name FROM students ORDER BY roll_number)
WHERE ROWNUM <= 3;
Output:
Explanation: The ORDERBY command will help us to overcome the sorting problem. As ROWNUM operates, ordering the data may cause some errors. To fix the error, OrderBy will make sure the data is ordered before sorting.
Method 3: Using ROW_NUMBER in Oracle SQL
The ROW_NUMBER is the alternative and most efficient method than ROWNUM. The row_number will sort or limit the data between any two rows; for example, here we sorted the data between rows 3 and 5.This is very efficient while handling larger datasets.
Example:
SELECT *
FROM (
SELECT students.*, ROW_NUMBER() OVER (ORDER BY roll_number) AS rn
FROM students
)
WHERE rn BETWEEN 3 AND 5;
Output:
Explanation: The ROW_NUMBER returns the row by condition, like here we fetched the rows between 3 and 5.
Method 4: Using DENSE_RANK() FUNCTION in Oracle SQL
We can use the DENSE_RANK() function in Oracle SQL with the subquery WHERE clause to limit the number of rows returned after sorting the data.
Example: Let’s first create a student table and update it with student marks to get the rank of the students.
--Create the students table
CREATE TABLE students (
roll_number NUMBER PRIMARY KEY,
name VARCHAR2(100)
);
--Insert student records
INSERT INTO students VALUES (1, 'Arun');
INSERT INTO students VALUES (2, 'Babu');
INSERT INTO students VALUES (3, 'Munna');
INSERT INTO students VALUES (4, 'Kishore');
INSERT INTO students VALUES (5, 'Vimal');
COMMIT;
--Alter the table to add marks
ALTER TABLE students ADD marks NUMBER;
--Update the student marks
UPDATE students SET marks = 85 WHERE roll_number = 1;
UPDATE students SET marks = 90 WHERE roll_number = 2;
UPDATE students SET marks = 85 WHERE roll_number = 3;
UPDATE students SET marks = 95 WHERE roll_number = 4;
UPDATE students SET marks = 90 WHERE roll_number = 5;
COMMIT;
--Format output for better readability
COLUMN roll_number FORMAT 9999 HEADING 'ROLL NO'
COLUMN name FORMAT A20 HEADING 'STUDENT NAME'
COLUMN marks FORMAT 999 HEADING 'MARKS'
Output:
After updating the marks to the table, it looks like this. Now, let’s get the first two ranks using dense_rank.
–Fetch top 2 ranks based on marks using DENSE_RANK()
SELECT roll_number, name, marks
FROM (
SELECT roll_number, name, marks,
DENSE_RANK() OVER (ORDER BY marks DESC) AS rnk
FROM students
)
WHERE rnk <= 2;
Output:
Explanation: The DENSE_RANK() subquery WHERE ordered the student data by their ranks and then limited the rows to only the top 2 rank students.
Method 5: Using NTILE() in Oracle SQL
The NTILE() method in Oracle SQL is used to segregate rows by buckets. It is very useful for ranking, calculations, and breaking into equal parts.
Example:
SET SERVEROUTPUT ON;
SELECT roll number, name, bucket
FROM (
SELECT roll_number, name, NTILE(3) OVER (ORDER BY roll_number) AS bucket
FROM students
)
WHERE bucket = 1;
Output:
Explanation:
The NTILE() function divides the rows into segments of buckets and produces the output based on conditions.
|
Methods |
Sorting |
Efficiency |
Use case |
| FETCH…OFFSET | Easily sort large datasets | Faster and easily accessible data | Efficient in row limiting and converting large sets of data into smaller ones ( |
| Rownum using orderby | Moderate sorting will not be recommended for getting proper output | Even after sorting output cannot be trusted or it may produce the wrong output | Ordered row limiting works in an older version of Oracle SQL. |
| ROW_NUMBER | Moderate sorting alternative for rownum | Moderate work but ensures ordered data. | Efficient in row-limiting |
To Limit the Number of Rows in Larger Datasets by Oracle SQL
Use of indexes in the dataset: We can use indexes to sort the data based on the index value. It will be easier to sort large datasets by their index number.
Example:
SELECT row_num, roll_number, name
FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY roll_number) AS row_num,
roll_number,
name
FROM students
)
WHERE row_num <= 3;
Output:
Explanation: In this example, we have limited the rows of the first 3 indexes.
Real-world Examples
1. Using pagination for Web applications: The pagination, which means splitting the larger data into smaller chunks, will be useful to fetch data from particular pages using UI (user interface). We can generate the buttons to go to the required pages.
Example:
SELECT *
FROM students
ORDER BY roll_number
OFFSET 1 ROWS FETCH NEXT 3 ROWS ONLY;
Output:
Explanation: This removes row 1 from the table and displays the next 3 rows. This method is defined as pagination, which breaks down larger datasets into smaller datasets for easy optimization.
2. Finding the second highest roll_number: This will be useful when you want to sort data based on ranking.
Example:
SELECT roll_number, name
FROM (
SELECT roll_number, name, ROW_NUMBER() OVER (ORDER BY roll_number DESC) AS rn
FROM students
)
WHERE rn = 2;
Output:
Explanation: This fetched the data of the students who ranked first by calculating the marks they obtained and displayed the result.
Conclusion
The FETCH command is very helpful in sorting the data efficiently. It fetches data based on the condition given after ordering them. For the ordered dataset, ROW_NUMBER can be used to limit the rows based on the condition. ROWNUM is not advisable when we can use ROW_NUMBER because rownum works before sorting while it may create some errors during initialization. Indexes can be used to sort, order, or limit the data in a query, which makes the dataset work efficiently. Understanding these approaches helps retrieve only the required number of sorted rows.