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:
rownum_10.png
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.
Fetch_10.png
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.
offset.png
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:

Enquire now

Give us a call or fill in the form below and we will contact you. We endeavor to answer all inquiries within 24 hours on business days.