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?
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.
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;
Loved by over 4 million developers and more than 90,000 organizations worldwide, Sentry provides code-level observability to many of the world’s best-known companies like Disney, Peloton, Cloudflare, Eventbrite, Slack, Supercell, and Rockstar Games. Each month we process billions of exceptions from the most popular products on the internet.