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