# Oracle Limit Rows Returned by a Query

> Use OFFSET...FETCH NEXT on Oracle 12c+, or a ROWNUM subquery on older versions, to page through query results

**URL:** https://sentry.io/answers/in-oracle-how-do-i-limit-the-rows-returned-by-a-query/

---

## The Problem

In MySQL, you can select a range of rows in a query of ordered results. For example:

```sql
SELECT *
FROM Person
ORDER BY Id
LIMIT 5,5 -- skip 5, take 5
```

In PostgreSQL you can use:

```sql
SELECT *
FROM Person
ORDER BY Id
LIMIT 5
OFFSET 5;
```

What's the equivalent syntax in Oracle's database server to limit the number of rows returned, or select a specific range?

## The Solution

Let's use an example table to demonstrate the command:

```sql
CREATE TABLE Person
(
    Id   INT PRIMARY KEY,
    Name VARCHAR(255)
);

INSERT INTO Person (Id, Name) VALUES (1, 'Amir');
INSERT INTO Person (Id, Name) VALUES (2, 'Sofia');
INSERT INTO Person (Id, Name) VALUES (3, 'Aya');
INSERT INTO Person (Id, Name) VALUES (4, 'Mateo');
INSERT INTO Person (Id, Name) VALUES (5, 'Leila');
INSERT INTO Person (Id, Name) VALUES (6, 'Yara');
INSERT INTO Person (Id, Name) VALUES (7, 'Ndidi');
INSERT INTO Person (Id, Name) VALUES (8, 'Santiago');
```

Modern Oracle (version 12 from 2013 onwards) implements the SQL:2008 standard, so we can use the command:

```sql
SELECT *
FROM Person
ORDER BY Id
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY;

-- Results:

-- ID|NAME    |
-- --+--------+
-- 6|Yara     |
-- 7|Ndidi    |
-- 8|Santiago |
```

You can use percentages of the table size too:

```sql
SELECT *
FROM Person
ORDER BY Id
OFFSET 5 ROWS
FETCH NEXT 50 PERCENT ROWS ONLY;
```

The exact syntax is:

```
[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
    { ROW | ROWS } { ONLY | WITH TIES } ]
```

It's documented at https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/SELECT.html.

### Old Oracle

If you are using Oracle from before 2013 you have to use a sub-select:

```sql
SELECT *
FROM
  (
    SELECT *
    FROM Person
    ORDER BY Id
  )
WHERE ROWNUM <= 5;
```

---

*Source: [sentry.io/answers/in-oracle-how-do-i-limit-the-rows-returned-by-a-query/](https://sentry.io/answers/in-oracle-how-do-i-limit-the-rows-returned-by-a-query/)*