How to Limit the Number of Rows Returned by an Oracle Query

When working with large datasets in Oracle, there are times when you might want to limit the number of rows returned by a query. Limiting rows can improve query performance, simplify testing, or prevent overwhelming users with too much data. Oracle provides several ways to restrict the number of rows in a query result, depending on your specific requirements.

In this article, we’ll explore how to limit the number of rows returned by an Oracle query using different techniques, including ROWNUM, FETCH FIRST, and ROW_NUMBER().

1. Using ROWNUM to Limit Rows

The ROWNUM pseudo-column is one of the simplest ways to limit the number of rows returned by an Oracle query. It returns a unique number for each row, starting at 1 for the first row.

Basic Syntax:

Here, n represents the number of rows you want to return.

Example:

This query will return the first 5 rows from the employees table.

Explanation:

• The ROWNUM condition filters the result set to only include the first n rows based on the order the database retrieves them.
• Note that ROWNUM is assigned to rows before any ORDER BY clause is applied. This can cause unexpected results if you need the rows to be limited after sorting.

Output (Example):

2. Using FETCH FIRST to Limit Rows

In Oracle 12c and later, Oracle introduced the FETCH FIRST syntax as part of the ANSI SQL standard, which provides a more intuitive and flexible way to limit rows. It is commonly used when combined with the ORDER BY clause.

Basic Syntax:

Example:

Explanation:

• FETCH FIRST n ROWS ONLY limits the result set to the first n rows after sorting the data according to the ORDER BY clause.
• This method makes it easy to get the “top” rows based on specific ordering criteria (e.g., highest salary, most recent records).

Output (Example):

The result is the top 5 employees ordered by salary in descending order.

3. Using ROW_NUMBER() for More Control

The ROW_NUMBER() analytic function provides a more flexible way to assign a unique row number to each row in the result set, based on a specified sorting order. You can then use this row number to filter the result.

Basic Syntax:

Example:

Explanation:

• The ROW_NUMBER() function assigns a unique row number to each row based on the ORDER BY clause.
• By using a subquery, you can filter the rows based on the row_num column after the rows have been assigned their row numbers. This ensures that the sorting is applied before limiting the rows.

Output (Example):

This approach is particularly useful when you need more control over the rows being returned, such as filtering after applying complex ordering or partitioning.

4. Using RANK() or DENSE_RANK() for Tied Rows

In cases where multiple rows might share the same rank, you can use RANK() or DENSE_RANK() functions to deal with ties. RANK() will leave gaps in ranking numbers if there are ties, while DENSE_RANK() will assign consecutive ranks to tied rows.

Example:

This approach ensures that if two employees share the same salary, both will be included, with proper handling of ties.

5. Considerations When Limiting Rows

• Performance: Limiting the number of rows returned by a query can improve query performance, especially when dealing with large datasets. Always ensure that the query is optimized to return only the necessary rows.
• Sorting: If you need to limit rows after sorting the data (e.g., highest salary or latest records), it is crucial to use an ORDER BY clause to control the result order before applying the limit.
• Pagination: For queries with many rows, you may want to paginate the results (e.g., displaying results in chunks). This can be done using ROW_NUMBER() in combination with the BETWEEN clause to fetch specific ranges of rows.

Limiting the number of rows returned by an Oracle query is an essential technique to manage large datasets and improve query performance. Depending on the version of Oracle you are using, and the specific requirements of your query, you can choose from various methods, including:

• ROWNUM: Simple method for limiting rows, but does not support complex ordering.
• FETCH FIRST: More flexible and part of the ANSI SQL standard, ideal for limiting rows after sorting.
• ROW_NUMBER(): Provides greater flexibility for complex queries, particularly when sorting and filtering are involved.
• RANK() and DENSE_RANK(): Useful for handling ties when limiting rows based on rank.

Choose the appropriate method based on your specific needs and Oracle version to limit rows effectively and optimize your queries.