Query

How Oracle Limit Rows Returned

Oracle Limit Rows

In MySQL, we use LIMIT clause to limit number of rows returned in SELECT SQL statements. But in Oracle, we may need to do more works to limit our target rows.

For comparing the expected result, we selected all data and sorted by the third column like the following:

SQL> conn hr/hr;
Connected.
SQL> set pagesize 13;
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

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
        129 Laura                Bissot
        169 Harrison             Bloom
        185 Alexis               Bull
        187 Anthony              Cabrio
        154 Nanette              Cambrault
        148 Gerald               Cambrault
        110 John                 Chen
        188 Kelly                Chung
        119 Karen                Colmenares
        142 Curtis               Davies
...
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.

Limit First Few Rows

Oracle Limit Rows Returned - Fetch First Few Rows
Oracle Limit Rows Returned – Fetch First Few Rows

ROWNUM

When we just want a portion of data to be returned, we should limit number of rows returned using ROWNUM as a predicate in SELECT, which is an Oracle 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
...

10 rows selected.

Even though ROWNUM is working, the result is NOT what we expected. That is the returned rows are not confined or narrowed down.

ROWNUM with 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
...

10 rows selected.

This is how Oracle limit rows returned in SQL statements without using advanced syntax.

FETCH FIRST X 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 FIRST X ROWS ONLY to confine returned rows.

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
...

10 rows selected.

Limit a Range of Rows

Oracle Limit Rows Returned - Fetch a Range of Rows
Oracle Limit Rows Returned – Fetch a Range of Rows

Sometimes, you may want to return a range of rows for separate web page browsing, for example, page 1 is for row 1 to 10 and page 2 is for row 11 to 20. In such case, you need to know how to fetch a range of rows.

OFFSET X ROWS FETCH NEXT Y ROWS ONLY

Suppose a visitor was trying to browsing the page 2 which is narrowed from row 11 to 20, then we can return a range of rows by this:

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

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
        129 Laura                Bissot
        169 Harrison             Bloom
        185 Alexis               Bull
        187 Anthony              Cabrio
        148 Gerald               Cambrault
        154 Nanette              Cambrault
        110 John                 Chen
        188 Kelly                Chung
        119 Karen                Colmenares
        142 Curtis               Davies

10 rows selected.

The web page confined the returned rows from “Bissot” to “Davies”, this is what we expected.

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

Leave a Reply

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