Does Top Work in Oracle?

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.

• When Was Aquaculture Started? An In-Depth Look at the Origins and Evolution of Fish Farming Through the Ages
• Which US State Is Farthest North?
• Could a Monk Become a Priest?

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.

• Are Moen and Pfister Interchangeable? (Explained)
• Does a Bag of Pennies Keep Flies Away??
• How Do Cormorants Catch Fish?

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.

Editorial Team

The Editorial Team at AnswerCatch.com brings you insightful and accurate content on a wide range of topics. Our diverse team of talented writers is passionate about providing you with the best possible reading experience.