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.

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.

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.

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.

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.

— michael.data@eipsoftware.com