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: curtisbl@gmail.com

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Martins Blog

Trying to explain complex things in simple terms

Oracle Data Warrior

Changing the world, one data model at a time. How can I help you?

Maaz Anjum's Blog

A life yet to be lived...

PeteWhoDidNotTweet

Stuff that interests me, if not you!

The Oracle Instructor

Explain, Exemplify, Empower

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

Oracle Spin - Flimatech Blog

Sharing Our Database Experience

Oracle Scratchpad

Just another Oracle weblog

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Follow

Get every new post delivered to your Inbox.

Join 1,457 other followers

%d bloggers like this: