How many times have you tried to query just for the first few rows with a SQL statement like:
SELECT id, brewery, city, state, country FROM barmaid.beers WHERE rownum <10
In this first part of discussing Oracle’s new native SQL support for limiting rows, we are going to look at the FETCH and OFFSET options for the SELECT statement. With the release of Oracle Database 12c, Oracle has introduced this new SQL syntax to simplify fetching the first few rows.
The following statement returns the first 10 rows from the barrmaid.beers table.
SELECT id, brewery, city, state, country FROM barmaid.beer FETCH FIRST 10 ROWS ONLY;
Results. They actually look the same; however, I got the 10th row as well.
Part of this new SQL clause, we can specify an OFFSET. The OFFSET will then skip that number of rows and return the next rows fetched.
SELECT id, brewery, city, state, country FROM barmaid.beer OFFSET 10 ROWS FETCH FIRST 5 ROWS ONLY;
Hopefully, these examples have given you the basics of using the OFFSET and FETCH options of the SELECT statement.
Email: [email protected]
I’m Bobby Curtis and I’m just your normal average guy who has been working in the technology field for awhile (started when I was 18 with the US Army). The goal of this blog has changed a bit over the years. Initially, it was a general blog where I wrote thoughts down. Then it changed to focus on the Oracle Database, Oracle Enterprise Manager, and eventually Oracle GoldenGate.
If you want to follow me on a more timely manner, I can be followed on twitter at @dbasolved or on LinkedIn under “Bobby Curtis MBA”.