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.<\/p>\n\n\n\n
MySQL uses the LIMIT <\/strong>operator and Microsoft T-SQL uses the TOP<\/strong> operator. In Oracle 12c, Oracle introduced the FETCH FIRST<\/strong> operator. Let’s see how it works.<\/p>\n\n\n\n\n\n\n\n At the end of a SELECT<\/strong> statement you can use the FETCH FIRST<\/strong> operator to limit the total number of rows returned.<\/p>\n\n\n\n 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<\/strong>. You need to use the ORDER BY <\/strong>operator to ensure correct results.<\/p>\n\n\n\n Another option with the FETCH<\/strong> operator is return a percentage of rows with PERCENT<\/strong> option.<\/p>\n\n\n\n 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<\/strong> to FETCH NEXT<\/strong>. You need to use the ORDER BY <\/strong>operator to ensure correct results.<\/p>\n\n\n\n I hope you found the above informative, let me know in the comments below.<\/p>\n\n\n\nSELECT eventrequest.eventno AS event_number\n ,eventrequest.dateheld\n ,COUNT(eventplan.planno) AS count_event_plans\nFROM eventrequest\n INNER JOIN eventplan\n ON eventrequest.eventno = eventplan.eventno\nWHERE eventplan.workdate BETWEEN '01-DEC-2013' AND '31-DEC-2013'\nGROUP BY eventrequest.eventno\n ,eventrequest.dateheld \nFETCH FIRST 5 ROWS ONLY;<\/pre>\n\n\n\n
SELECT eventrequest.eventno AS event_number\n ,eventrequest.dateheld\n ,COUNT(eventplan.planno) AS count_event_plans\nFROM eventrequest\n INNER JOIN eventplan\n ON eventrequest.eventno = eventplan.eventno\nWHERE eventplan.workdate BETWEEN '01-DEC-2013' AND '31-DEC-2013'\nGROUP BY eventrequest.eventno\n ,eventrequest.dateheld\nORDER BY 1,2 \nFETCH FIRST 5 ROWS ONLY WITH TIES;<\/pre>\n\n\n\n
SELECT eventrequest.eventno AS event_number\n ,eventrequest.dateheld\n ,COUNT(eventplan.planno) AS count_event_plans\nFROM eventrequest\n INNER JOIN eventplan\n ON eventrequest.eventno = eventplan.eventno\nWHERE eventplan.workdate BETWEEN '01-DEC-2013' AND '31-DEC-2013'\nGROUP BY eventrequest.eventno\n ,eventrequest.dateheld \nFETCH FIRST 10 PERCENT ONLY;<\/pre>\n\n\n\n
SELECT eventrequest.eventno AS event_number\n ,eventrequest.dateheld\n ,COUNT(eventplan.planno) AS count_event_plans\nFROM eventrequest\n INNER JOIN eventplan\n ON eventrequest.eventno = eventplan.eventno\nWHERE eventplan.workdate BETWEEN '01-DEC-2013' AND '31-DEC-2013'\nGROUP BY eventrequest.eventno\n ,eventrequest.dateheld \nORDER BY 1,2 \nOFFSET 25 ROWS FETCH NEXT 25 ROWS ONLY;<\/pre>\n\n\n\n