Limit Rows Returned

Previous to Oracle 12c limit the number of rows returned usually meant encapsulating the query as a subquery.  It wasn’t particularly nice looking and was out of alignment with other relational database query languages.

MySQL uses the LIMIT operator and Microsoft T-SQL uses the TOP operator. In Oracle 12c, Oracle introduced the FETCH FIRST operator.  Let’s see how it works.

At the end of a SELECT statement you can use the FETCH FIRST operator to limit the total number of rows returned.

Alternatively if we want to return the first 5 rows and include any additional rows that the same value in count_event_plans we can use the option WITH TIES. You need to use the ORDER BY operator to ensure correct results.

Another option with the FETCH operator is return a percentage of rows with PERCENT option.

Lastly we may want to paginate the results or start 25 rows from the start of the query results. A key note here is that you need to change from FETCH FIRST to FETCH NEXT.  You need to use the ORDER BY operator to ensure correct results.

I hope you found the above informative, let me know in the comments below.

— michael.data@eipsoftware.com