I only want these first rows – 12c edition – Part 1

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

Results:

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;

Results.

Hopefully, these examples have given you the basics of using the OFFSET and FETCH options of the SELECT statement.
Enjoy!
Twitter: @curtisbl294
Blog: http://dbasolved.com
Email: [email protected]

Please follow and like: