Query

How to Limit Number of Rows Returned in Oracle

Limit Number of Rows Returned

In MySQL, we use LIMIT n cluase to limit number of rows returned. In Oracle, we may need to do more works to fetch our target rows.

First, we listed all sorted data like the following:

SQL> conn hr/hr;
Connected.
SQL> select employee_id, first_name, last_name from employees order by 3;

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
        174 Ellen                Abel
        166 Sundar               Ande
        130 Mozhe                Atkinson
        105 David                Austin
        204 Hermann              Baer
        116 Shelli               Baida
        167 Amit                 Banda
        172 Elizabeth            Bates
        192 Sarah                Bell
        151 David                Bernstein
        129 Laura                Bissot
...
107 rows selected.

As you can see, entire table is listed no matter what. The result could scroll over the console page, we may miss our target rows eventually.

Use ROWNUM

When we just want part of data to be returned, we should limit number of rows returned using ROWNUM as a predicate, which is a pseudocolumn that represents the returned row sequence starting from 1. ROWNUM <= 10 means that we limit the first 10 rows to be returned.

SQL> select employee_id, first_name, last_name from employees where rownum <= 10 order by 3;

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
        204 Hermann              Baer
        202 Pat                  Fay
        206 William              Gietz
        199 Douglas              Grant
        201 Michael              Hartstein
        205 Shelley              Higgins
        100 Steven               King
        203 Susan                Mavris
        198 Donald               OConnell
        200 Jennifer             Whalen

10 rows selected.

Even though ROWNUM is working, the result is not what we expected.

Use ROWNUM in Subquery

The correct way is to use subquery to sort your data first, then limit number of rows returned.

SQL> select * from (select employee_id, first_name, last_name from employees order by 3) where rownum <= 10;

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
        174 Ellen                Abel
        166 Sundar               Ande
        130 Mozhe                Atkinson
        105 David                Austin
        204 Hermann              Baer
        116 Shelli               Baida
        167 Amit                 Banda
        172 Elizabeth            Bates
        192 Sarah                Bell
        151 David                Bernstein

10 rows selected.

Use FETCH NEXT ROWS ONLY

Since 12c, we have new row limiting clause that can meet our requirements without using subquery. In the following statement, we use FETCH NEXT ROWS ONLY to solve our problem.

SQL> select employee_id, first_name, last_name from employees order by 3 fetch first 10 rows only;

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
        174 Ellen                Abel
        166 Sundar               Ande
        130 Mozhe                Atkinson
        105 David                Austin
        204 Hermann              Baer
        116 Shelli               Baida
        167 Amit                 Banda
        172 Elizabeth            Bates
        192 Sarah                Bell
        151 David                Bernstein

10 rows selected.

Although the newly provided syntax is flexible for developers to fulfill their requirements, it’s still too complex, whereas MySQL’s LIMIT 10 is rather simple and straightforward.

Leave a Reply

Your email address will not be published. Required fields are marked *