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:

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

In PostgreSQL you can use:

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:

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:

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:

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:

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