How to limit the number of rows returned by an Oracle query after ordering

by

Posted on by

In MySQL, you can use “Limit n,m”, like this:

select *
from sometable
order by name
limit 20,10

How to do in Oracle?

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

like:

select * from
( select a.*, ROWNUM rnum from
  ( select * from sometable order by name ) a
  where ROWNUM <= 30)
where rnum  >= 20

In SQL Server: http://www.withdata.com/ad/sql-server/the-sql-server-equivalent-for-the-mysql-limit.html .

In DB2: http://www.withdata.com/ad/db2/the-db2-equivalent-for-the-mysql-limit.html .

In Mysql: http://www.withdata.com/ad/mysql/how-to-limit-the-number-of-rows-returned-by-an-mysql-query.html .

In Sqite: http://www.withdata.com/ad/sqlite/how-to-limit-the-number-of-rows-returned-by-an-sqlite-query.html .

In PostgreSQL: http://www.withdata.com/ad/postgresql/how-to-limit-the-number-of-rows-returned-by-an-postgresql-query.html .

You can visit this page for more information: http://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering

Some Oracle tools you can try: https://www.withdata.com/oracle/