How to limit the number of rows returned by a Query in Oracle

Last Updated on :April 30, 2021

Oracle database has a pseudo-column named ROWNUM. You can use this ROWNUM in your SQL query to limit the results

You can use query like below to limit the number of returning rows

SELECT * FROM employees WHERE ROWNUM <=5

If you want to sort 10 record by salary, you need to use a subquery like this

select *
from
( select *
  from employees
  order by salary desc )
where ROWNUM <= 10;

If you want to limit the result in between range, for example you want to sort the resultset by salary and limit rows from 10 to 20 in the resultset.

SELECT *
FROM
  (SELECT a.*,
    ROWNUM rnum
  FROM
    (SELECT * FROM employees emp ORDER BY salary DESC
    )a
  WHERE ROWNUM <= 20
  )
WHERE rnum >=10;

You can refer below subquery syntax to limit the result set in-between the range.

select *
from ( select /*+ FIRST_ROWS(n) */
a.*, ROWNUM rnum
from ( your_query_goes_here,
with order by ) a
where ROWNUM <= 
      :MAX_ROW_TO_FETCH ) 
where rnum  >= :MIN_ROW_TO_FETCH;

MIN_ROW_TO_FETCH – This is lower bound of the limit, if you wanted the row from 10 to 20, you have to set this to 10
MAX_ROW_TO_FETCH – This is upper bound of the limit, if you wanted the row from 10 to 20, you have to set this to 20

Starting from Oracle 12c (12.1), there is a row limiting Clause. It can do the tasks more eaiser way than ROWNUM.

Below example to limit the row from 10 to 20 in the result set using OFFSET-FETCH Clause

SELECT * 
FROM employees emp 
ORDER BY salary DESC 
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

Here is the query to get first 5 rows

SELECT * 
FROM employees emp 
ORDER BY salary DESC 
FETCH FIRST 5 ROWS ONLY;

You can use percentage to specify the percentage of total number of rows to return. Below query will return 20% of total number of rows

SELECT * 
FROM employees emp 
ORDER BY salary DESC 
FETCH FIRST 20 PERCENT ROWS ONLY;