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.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT eventrequest.eventno AS event_number ,eventrequest.dateheld ,COUNT(eventplan.planno) AS count_event_plans FROM eventrequest INNER JOIN eventplan ON eventrequest.eventno = eventplan.eventno WHERE eventplan.workdate BETWEEN '01-DEC-2013' AND '31-DEC-2013' GROUP BY eventrequest.eventno ,eventrequest.dateheld FETCH FIRST 5 ROWS ONLY; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT eventrequest.eventno AS event_number ,eventrequest.dateheld ,COUNT(eventplan.planno) AS count_event_plans FROM eventrequest INNER JOIN eventplan ON eventrequest.eventno = eventplan.eventno WHERE eventplan.workdate BETWEEN '01-DEC-2013' AND '31-DEC-2013' GROUP BY eventrequest.eventno ,eventrequest.dateheld ORDER BY 1,2 FETCH FIRST 5 ROWS ONLY WITH TIES; |
Another option with the FETCH operator is return a percentage of rows with PERCENT option.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT eventrequest.eventno AS event_number ,eventrequest.dateheld ,COUNT(eventplan.planno) AS count_event_plans FROM eventrequest INNER JOIN eventplan ON eventrequest.eventno = eventplan.eventno WHERE eventplan.workdate BETWEEN '01-DEC-2013' AND '31-DEC-2013' GROUP BY eventrequest.eventno ,eventrequest.dateheld FETCH FIRST 10 PERCENT ONLY; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT eventrequest.eventno AS event_number ,eventrequest.dateheld ,COUNT(eventplan.planno) AS count_event_plans FROM eventrequest INNER JOIN eventplan ON eventrequest.eventno = eventplan.eventno WHERE eventplan.workdate BETWEEN '01-DEC-2013' AND '31-DEC-2013' GROUP BY eventrequest.eventno ,eventrequest.dateheld ORDER BY 1,2 OFFSET 25 ROWS FETCH NEXT 25 ROWS ONLY; |
I hope you found the above informative, let me know in the comments below.
Leave a Reply