Skip to content

Oracle Limit Rows Returned

  • by

Oracle Limit Query Returned

In MySQL, we use SQL 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 instead of SQL LIMIT in MySQL.

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, all content of this table is listed no matter what. For a large table, the returned result may scroll over the console page, we may miss our rows eventually. That's why we should restrict unwanted rows from being returned.

Oracle 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 take the first 10 rows maximum 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. This is because 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 the maximum 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 n ROWS ONLY

Since 12c, we have new row_limiting_clause that can meet our requirements without using subquery to narrow down the scope. In the following statement, we use FETCH FIRST n ROWS ONLY to limit and keep 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.

Oracle 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 specific 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 by the new bounding way.

OFFSET m ROWS FETCH NEXT n 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 current web page limited the returned rows from "Bissot" to "Davies", this is what we expected.

Although Oracle newly provided syntax for limiting returned rows is flexible for developers to fulfill their requirements, it's still too complex, whereas MySQL's SQL LIMIT syntax is rather simple and straightforward. Two thumb ups to MySQL's limit syntax.

Leave a Reply

Your email address will not be published.