When querying data in SQL databases like Oracle, MySQL, or SQL Server, we often want to limit the number of rows returned. In some databases like MySQL and SQL Server, we can use the TOP clause to retrieve only a specified number of rows from a query result set. However, Oracle does not support the TOP clause. So how do we limit rows in Oracle?
Using ROWNUM Instead of TOP in Oracle
Oracle provides the ROWNUM pseudocolumn to restrict the number of rows returned by a query. ROWNUM assigns a number to each row, starting from 1, as the rows are retrieved by the query.
The ROWNUM clause is applied after the rows have been selected, unlike the TOP clause which restricts rows before selection. So ROWNUM filters rows from the result set, while TOP limits which rows are added to the result set.
To use ROWNUM to limit rows, it must be used in the WHERE clause of the SELECT statement. The basic syntax is:
SELECT columns
FROM table
WHERE ROWNUM <= N;
For example, to retrieve the first 5 rows from the EMPLOYEES table, we can write:
SELECT *
FROM EMPLOYEES
WHERE ROWNUM <= 5;
This will return the first 5 rows from the EMPLOYEES table based on how the rows are ordered (default is ascending order of primary key).
The ROWNUM value is assigned after ordering, so we can control which rows are returned by specifying an ORDER BY clause:
SELECT *
FROM EMPLOYEES
WHERE ROWNUM <= 5
ORDER BY salary DESC;
Now the query will return the 5 employees with the highest salaries.
Key Differences Between ROWNUM and TOP
While both ROWNUM and TOP are used to limit the number of rows returned by a query, there are some key differences:
- ROWNUM is a pseudocolumn, while TOP is a clause. Pseudocolumns are not actual table columns but values generated by Oracle.
- TOP limits rows before selection while ROWNUM filters after selection. TOP restricts which rows are added to the result set, ROWNUM removes rows from the result set after retrieval.
- TOP allows specifying a percentage of rows, while ROWNUM only allows specifying number of rows. TOP 10 PERCENT will return 10% of the rows.
- ROWNUM is reset to 1 for each query block. If a query has nested subqueries, each subquery will start ROWNUM at 1. TOP applies to the entire query.
- ROWNUM cannot be used in a subquery. ROWNUM values are assigned only in the outer query. TOP can be used in subqueries.
So in summary, TOP gives more flexibility and occurs before row selection, while ROWNUM offers simple row limiting but after initial query execution.
Using ROWNUM Effectively in Oracle
Since ROWNUM is applied after the query executes, there are some specific techniques needed to use it effectively:
Retrieve Top N Rows
To retrieve the top N rows from a table, we need to filter on ROWNUM in an outer query:
SELECT *
FROM
(SELECT * FROM Employees
ORDER BY salary DESC)
WHERE ROWNUM <= 10;
The inner query orders the rows, then the outer query filters the first 10 rows by ROWNUM.
Pagination Queries
If we want to implement pagination to retrieve rows 21-30 for a page, we can use ROWNUM with an offset:
SELECT *
FROM
(SELECT *, ROWNUM AS rnum FROM Employees
ORDER BY salary DESC)
WHERE rnum >= 21 AND rnum <= 30;
Here we assign ROWNUM to a column alias RNUM to use it in the outer query to specify the offset.
Avoid WHERE ROWNUM Filtering
Filtering directly on ROWNUM in the WHERE clause will not work as expected:
SELECT *
FROM Employees
WHERE ROWNUM <= 10;
This will always return 0 rows. This is because the WHERE ROWNUM filter is applied after the ROWNUM values are generated, so no rows will have ROWNUM > 0 yet.
Cannot Use ROWNUM in Subqueries
Since ROWNUM values are generated only in the outer query, they cannot be referenced in subqueries:
SELECT *
FROM Employees e
WHERE e.salary >
(SELECT MAX(salary) FROM Employees
WHERE ROWNUM < 5);
This will result in an error because ROWNUM cannot be used in the subquery.
Alternative Options for Top N Queries in Oracle
While ROWNUM is the simplest way to limit rows in Oracle, there are some alternatives that offer more flexibility:
- FETCH FIRST n ROWS ONLY – Similar syntax to TOP, allows fetching first n rows after ORDER BY.
- ROW_NUMBER() analytic function – Assign row numbers partitioned by column value to filter in outer query.
- RANK() analytic function – Generates ranking that can be filtered in outer query.
Here is an example using the FETCH FIRST syntax:
SELECT *
FROM Employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;
And with RANK():
SELECT *
FROM
(SELECT *, RANK() OVER (ORDER BY salary DESC) rnk
FROM Employees)
WHERE rnk <= 5;
These alternatives avoid some of the ROWNUM restrictions and can offer more flexibility.
Conclusion
While the TOP clause is not supported in Oracle, the ROWNUM pseudocolumn provides a simple way to limit the number of rows returned by a query. ROWNUM gets assigned after rows are retrieved, so specific techniques like using an outer query are needed to filter results effectively. There are also alternative analytic functions like FETCH FIRST and RANK() that can achieve similar Top N row filtering.
Understanding the difference between TOP and ROWNUM behavior is key to writing effective row limiting queries in Oracle. With proper syntax and structuring queries to accommodate how ROWNUM works, we can successfully get back top rows or implement pagination in Oracle.
- Which Planet Spins Clockwise?
- How Do You Make a Pool Table Laugh?
- Why Is Mi Abuelo Es Argentina Incorrect?
- How Can You Uncover Your Buyer Personas Reading Habits?
- What Is the Best Crossword Solver?
- Are Bats Blind?
- Will Borosilicate Glass Break If Dropped?
- How Do Chefs Stay Cool in the Kitchen?
- How Can One Help a Targeted Individual? (Detailed Guide)
- Are Beekman 1802 Products Cruelty Free?
- Lilac Pointed Siamese Cat [Behaviour, Health and Cost]
- Which Fortified Cereals Have the Most Calcium?
- Does Ghostery Sell Your Data?
- When Was the Omicron Variant of COVID-19 Detected in the U.S.?
- What Is the Ramsay Hunt Syndrome? [Symptoms, Causes, Treatment]?
- Why Is My Dyson Airwrap Flashing Red?
- How to Delete TikTok Videos Without Losing Likes?
- How to Add SeaWorld Tickets to Apple Wallet?
- Should Keywords Be in Alphabetical Order?
- Which County Is Guildford In?
- Are Smithers and Burns a Couple?
- How Do I Get to My Yahoo Fantasy Football League?