How To Limit The Number Of Rows Returned In Oracle Database

If you work with databases, you know that the number of rows returned in a query can sometimes get overwhelming. In this article, we’re going to show you how to limit the number of rows returned in an Oracle database query so that you can work more efficiently and keep your data organized.

The ROWNUM Pseudocolumn

The most straightforward way to limit the number of rows returned in an Oracle database is to use the ROWNUM pseudocolumn. This pseudocolumn generates a unique number for each row returned in a query, and you can use it to limit the number of rows that are returned.

Here’s a simple example of how to use the ROWNUM pseudocolumn to return only the first 5 rows of a table:

SELECT *
FROM mytable
WHERE ROWNUM <= 5;

In this example, we’re selecting all columns from the mytable table, and we’re using the WHERE clause to limit the number of rows returned to 5 or less. The ROWNUM pseudocolumn is assigned to each row in the result set, and the WHERE clause filters out all rows with a ROWNUM greater than 5.

The FETCH FIRST Clause

If you’re using Oracle 12c or later, you can also use the FETCH FIRST clause to limit the number of rows returned. The FETCH FIRST clause works similarly to the LIMIT clause in other relational databases, and it provides a more intuitive syntax for limiting the number of rows returned.

Here’s an example of how to use the FETCH FIRST clause to return only the first 5 rows of a table:

SELECT *
FROM mytable
ORDER BY mycolumn
FETCH FIRST 5 ROWS ONLY;

In this example, we’re selecting all columns from the mytable table, and we’re using the FETCH FIRST clause to limit the number of rows returned to 5. The ORDER BY clause is optional, and it allows you to specify the order in which the rows are returned. If you don’t specify an ORDER BY clause, the rows will be returned in an undefined order.

Paginating with FETCH NEXT and OFFSET

Another option to limit the number of rows returned in an Oracle database query is to use the FETCH NEXT clause in combination with the OFFSET clause. This method allows you to skip a certain number of rows and return a specific number of rows after the skipped rows.

Here’s an example of how to use the FETCH NEXT clause with OFFSET to return rows 6 through 10 of a table:

SELECT *
FROM mytable
ORDER BY mycolumn
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY;

In this example, the OFFSET clause skips the first 5 rows returned by the query, and the FETCH NEXT clause returns the next 5 rows after the skipped rows. The ORDER BY clause is used to specify the order in which the rows are returned.

By using the FETCH NEXT clause with OFFSET, you can easily paginate your query results and retrieve a specific range of rows from a larger result set. This can be especially useful when working with large tables or when presenting query results to users in a user interface.

Conclusion

In this article, we’ve shown you two ways to limit the number of rows returned in an Oracle database: using the ROWNUM pseudocolumn and the FETCH FIRST clause. By using either of these methods, you can work more efficiently and keep your data organized. Whether you’re working with a large table or a complex query, limiting the number of rows returned is a simple and effective way to manage your data.