---
title: "2 Amazing Ways to Limit Rows in Oracle SQL Query"
description: "These two ways limit the number of rows return from an SQL query. These are helpful for Oracle and other SQL developers."
url: https://srinimf.com/2022/08/13/2-amazing-ways-to-limit-rows-in-oracle-sql-query/
date: 2022-08-13
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2022/08/pexels-photo-7034504.jpeg?fit=1880%2C1253&ssl=1
categories: ["SQL"]
tags: ["SQL to limit rows"]
type: post
lang: en
---

# 2 Amazing Ways to Limit Rows in Oracle SQL Query

Here are two top ideas to limit the rows in Oracle. Those are ROWNUM and the Fetch. Below, you will find examples on usage.

## Idea#1 – ROWNUM

Here is an SQL query [to get](https://srinimf.com/2021/11/06/how-to-get-cpu-time-for-sql-query/) a row using the ROWNUM. ROWNUM returns a number that indicates the position of a row in the table.

Related[3 Must-know Approaches to Delete a Table](https://srinimf.com/2023/01/02/3-approaches-to-delete-table-in-sql/)

Precisely, the first row selected has a ROWNUM of 1, the second row has a ROWNUM of 2, and so on. You can use ROWNUM in the WHERE clause of a SELECT statement to limit the number of rows retrieved.

Related[2 Exceptional SQL Queries Demonstrating Usage of AND and OR in WHERE Clause](https://srinimf.com/2022/11/06/sql-where-the-and-and-or-usage-query-examples/)

```
select * from emp where rownum = 1
select * from emp where rownum < 3
```

## Idea#2 – Fetch

Indeed, the recent development in Oracle 12c is the row limiting clause lets us easily specify how many results to return from a query. We can choose either several rows or a percentage of rows. The number can be exact.

Even more important, the rows returned by the row limiting clause will respect the order-by-clause if there is one. Without the order by clause, the syntax will return the first N rows it finds. For example, let’s get the first three satellites based on their launch date.

```
--First 3 satellites.
select
   to_char(launch_date, 'YYYY-MM-DD') launch_date,
   official_name
from satellite
join launch
   on satellite.launch_id = launch.launch_id
order by launch_date, official_name
fetch first 3 rows only;
```

Related

- [How to Write SQL Query to Get Even and Odd Records](https://srinimf.com/2022/04/30/how-to-write-sql-query-to-get-even-and-odd-records/)DateApril 30, 2022
- [How to Check if Table Exists in any RDBMS](https://srinimf.com/2018/07/18/3-top-sql-queries-you-can-find-table-exists-in-oracle/)DateJuly 18, 2018
- [32 Complex SQL Interview Questions](https://srinimf.com/2012/07/25/complex-sql-queries/)DateJuly 25, 2012

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2022%2F08%2F13%2F2-amazing-ways-to-limit-rows-in-oracle-sql-query%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2022%2F08%2Fpexels-photo-7034504.jpeg%3Ffit%3D1200%252C800%26ssl%3D1&description=2%20Amazing%20Ways%20to%20Limit%20Rows%20in%20Oracle%20SQL%20Query)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2022/08/13/2-amazing-ways-to-limit-rows-in-oracle-sql-query/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2022/08/13/2-amazing-ways-to-limit-rows-in-oracle-sql-query/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2022/08/13/2-amazing-ways-to-limit-rows-in-oracle-sql-query/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2022/08/13/2-amazing-ways-to-limit-rows-in-oracle-sql-query/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%202%20Amazing%20Ways%20to%20Limit%20Rows%20in%20Oracle%20SQL%20Query&body=https%3A%2F%2Fsrinimf.com%2F2022%2F08%2F13%2F2-amazing-ways-to-limit-rows-in-oracle-sql-query%2F&share=email)
-

### Like this:

Like Loading…