There are two approaches to limit the number of rows returned by an Oracle query. Using the rownum value to limit the number of rows, as well as the OFFSET and FETCH keywords to limit the number of rows. The row limiting clause is supported in Oracle queries beginning with Oracle 12c R1 (12.1). Oracle does not use the LIMIT keyword.
As a result of the select query, all rows will be returned. The limit option is used when you want to limit the number of rows based on your needs. Restrict the number of rows will limit the number of rows returned by the query. This will limit the number of rows that are not necessary or utilized in the application.
In Oracle, you may use the pseudo-column ROWNUM to limit the number of rows returned by a Query. The pseudo-column ROWNUM in Oracle returns a number indicating the order in which the database picks a row from a table. A ROWNUM value is assigned to a row after it has passed the query’s filter predicates but before query aggregation or sorting.
Problem in limit the number of rows
The query below attempts to limit the number of rows after sorting them in descending order in the select query. However, in the select query, the rownum is generated before the ordering. The query selects the top ten rows and then applies the order clause to the results.The query does not provide the desired results.
select * from emp where rownum <= 10 order by 1 desc;
Solution 1
The subquery is the simplest method for limiting the number of rows returned by a query. The subquery will get the appropriate rows, and the limit will be applied to the subquery result. This technique is inefficient since it loads all of the rows from the subquery. This subquery approach is used when your subquery provides an optimized result.
select * from
( select * from emp order by 1 desc )
where ROWNUM <= 5;
If you need to fetch rows for pagination, provide the number of rows in between the results. The first row and the number of rows must be given.
select * from
(
select e.*, ROWNUM rnum from
( select * from emp order by 1 desc ) e
where ROWNUM <= 40
)
where rnum >= 31
select * from
( select e.*, ROWNUM rnum from emp e order by 1 desc )
where rnum >= 31 and rnum<=40;
Solution 2
The row limiting clause is supported in Oracle queries starting with Oracle 12c R1 (12.1). Oracle does not use the LIMIT keyword; instead, it use the OFFSET and FETCH keywords. The query below will use offset and fetch to limit the number of rows returned.
select * from emp order by 1 desc fetch next 10 rows only;
The above query will limit the number of rows to first 10 rows. The below query will return a pagination rows limit in between rows.
select * from emp order by 1 desc offset 20 rows fetch next 10 rows only;
Solution 3
In the solution 2, the query will return exactly the number of rows configured in the fetch. If there are duplicate values, the query will not return all the rows.
For example, if 15 employees has same name in the employee table, the query will fetch first 10 employees. If you want the complete list of all the 15 employees then the query should use with ties option.
select * from emp order by 1 desc fetch next 10 rows with ties;
If no duplicate in employee name, the it returns only 10 rows.
Solution 4
Oracle supports to limit the number of rows by percentage. The below query will return top 10 percent of rows as a output. This will not limit the number of rows. The query fetch all the rows which comes under top 10 percent.
select * from emp order by 1 desc fetch first 10 percent rows only;
Solution 5
In the n percent results, the offset can be used. The query will return n% of the rows. The offset will be included in the query result. Using this query, you may get the bottom border of the rows inside the percent result.
select * from emp order by 1 desc offset 20 rows fetch first 10 percent rows only;
Solution 6
The row number() function is used to arrange the rows. The row order will be utilized to retrieve and limit the number of rows returned by Oracle. The query below demonstrates how to sort and limit the rows. The query will be rigid using this way.
select * from
(
select e.*, Row_Number() OVER (order by 1) rownumber from emp e
)
select rownumber between 1 and 20;