Response: You can restrict the count of rows returned by an Oracle query subsequent to sorting by utilizing FETCH or ROWNUM queries.
Restricting the count of rows returned by an Oracle query after sorting is crucial as it enhances data performance and minimizes loading duration. In this article, we will delve into various methods to restrict the count of rows returned by an Oracle query after sorting, providing examples for each approach.
Table of Contents:
- Techniques to Limit the Rows Returned by an Oracle Query after Sorting
- Comparison of Performance for Each Technique
- Restricting the Number of Rows in More Extensive Datasets via Oracle SQL
- Practical Examples
- Summary
Techniques to Limit the Rows Returned by an Oracle Query after Sorting
Initially, let’s establish a dataset to demonstrate how to restrict the number of rows returned by an Oracle query post-sorting, which will be used for all examples of the different techniques.
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 improved 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 table contains the names and roll numbers of the students we have created.
The Oracle database offers several methods to limit the count of rows in a query output after sorting, guaranteeing efficient data access.
Technique 1: Employing the FETCH command in Oracle SQL
The FETCH command retrieves the specified quantity of rows from a dataset following the data’s ordering.
Example:
SELECT * FROM students ORDER BY roll_number FETCH FIRST 3 ROWS ONLY;Output:
Explanation: The FETCH command shown here limits the output to the first 3 rows while excluding the next 2 rows.
Using OFFSET in FETCH
The OFFSET command is employed to skip rows when fetching data. For instance, if we want to display the last 2 rows after neglecting the first 3 rows, OFFSET can be utilized for that purpose.
Example:
SELECT *
FROM students
ORDER BY roll_number
OFFSET 3 ROWS FETCH NEXT 2 ROWS ONLY;Output:
Explanation: Here, OFFSET excludes the first 3 rows and presents the subsequent 2 rows following their removal.
Technique 2: Utilizing ORDER BY with ROWNUM in Oracle SQL
The ORDER BY clause in ROWNUM allocates values to the rows before executing sorting. However, this may lead to errors during retrieval. To rectify this, using subqueries with WHERE is advisable.
Example:
SELECT *
FROM (SELECT roll_number, name FROM students ORDER BY roll_number)
WHERE ROWNUM Output:
Explanation: The ORDER BY clause assists in resolving the sorting issue. Since ROWNUM acts prior to the order, ordering the data can result in errors. Employing ORDER BY ensures that data is sorted correctly before applying ROWNUM.
Technique 3: Applying ROW_NUMBER in Oracle SQL
The ROW_NUMBER function is a more effective alternative than ROWNUM. This function sorts or limits data between two specified rows; for example, we sorted the data between rows 3 and 5. This method is particularly efficient when managing large datasets.
Example:
SELECT *
FROM (
SELECT students.*, ROW_NUMBER() OVER (ORDER BY roll_number) AS rn
FROM students
)
WHERE rn BETWEEN 3 AND 5;Output:
Clarification: The ROW_NUMBER function provides the row based on specified criteria; for instance, here we retrieved rows numbered from 3 to 5.
Method 4: Employing DENSE_RANK() FUNCTION in Oracle SQL
We have the capability to apply the DENSE_RANK() function in Oracle SQL alongside the subquery WHERE clause to restrict the quantity of rows produced after the data has been sorted.
Illustration: First, we will construct a student table and populate it with student scores to establish the rankings of the students.
--Establish the students table
CREATE TABLE students (
roll_number NUMBER PRIMARY KEY,
name VARCHAR2(100)
);
--Insert student entries
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;
--Modify the table to integrate marks
ALTER TABLE students ADD marks NUMBER;
--Revise the student scores
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;
--Adjust output for enhanced readability
COLUMN roll_number FORMAT 9999 HEADING 'ROLL NO'
COLUMN name FORMAT A20 HEADING 'STUDENT NAME'
COLUMN marks FORMAT 999 HEADING 'MARKS'Result:
Upon updating the table with the scores, it appears as shown. Now, let us acquire the top two ranks using dense_rank.
–Retrieve 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;Result:
Clarification: The DENSE_RANK() subquery WHERE organized the student data following their ranks and then constrained the output to only the top 2 ranked students.
Method 5: Utilizing NTILE() in Oracle SQL
The NTILE() function in Oracle SQL serves to categorize rows into buckets. It is highly beneficial for ranking, calculations, and partitioning data into equal segments.
Illustration:
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;Result:
Clarification:
The NTILE() function subdivides the rows into segments of buckets and generates output based on the stipulated conditions.
Comparison of Performance for Each Method
| Methods | Sorting | Effectiveness | Use case |
| FETCH…OFFSET | Effortlessly sorts extensive datasets | Quicker and more readily accessible data | Effective in limiting rows and transforming larger datasets into smaller sections ( |
| Rownum using orderby | Moderate sorting not advisable for achieving accurate output | Even post-sorting, the output may not be reliable or could yield erroneous results | Ordered row limitation functions in earlier versions of Oracle SQL. |
| ROW_NUMBER | A moderate sorting alternative for rownum | Moderate workload while ensuring ordered data. | Effective in limiting rows |
To Restrict the Quantity of Rows in Larger Datasets by Oracle SQL
Application of indexes in the dataset: Indexes can be utilized to sort data based on index values, facilitating easier sorting of large datasets according to their index numbers.
Illustration:
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;Result:
Clarification: In this scenario, we have constrained the rows to the first 3 indexes.
Practical Applications
1. Implementing pagination in Web applications: Pagination, which entails dividing vast data into smaller segments, proves beneficial for retrieving data from specific pages via the user interface (UI). Buttons can be generated to navigate to the desired pages.
Illustration:
SELECT *
FROM students
ORDER BY roll_number
OFFSET 1 ROWS FETCH NEXT 3 ROWS ONLY;Result:
Clarification: This excludes the first row from the table and showcases the next three rows. This approach is recognized as pagination, which dissects vast datasets into manageable sizes for improved optimization.
2. Identifying the second highest roll_number: This method is valuable when seeking to organize data based on rank.
Illustration:
SELECT roll_number, name
FROM (
SELECT roll_number, name, ROW_NUMBER() OVER (ORDER BY roll_number DESC) AS rn
FROM students
)
WHERE rn = 2;Result:
Clarification: This retrieved data concerning the students who placed first based on the scores they achieved, presenting the outcome.
Final Thoughts
The FETCH command is significantly useful for efficiently arranging data. It retrieves data in accordance with the conditions set following their sequence. In an ordered dataset, ROW_NUMBER can be employed to limit rows based on specified conditions. ROWNUM is not recommended when ROW_NUMBER can be utilized as rownum operates prior to sorting, which may lead to errors during initialization. Indexes may be applied to sort, arrange, or restrict data within a query, optimizing the operational efficiency of datasets. Comprehending these methodologies is essential for acquiring only the necessary number of ordered rows.
The article How to limit the rows returned by an Oracle query after ordering? first appeared on Intellipaat Blog.