• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

mysql limit and oracle rownum to limit resultset

 
zohaib khan
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I am able to limit resultset in pagination using mysql but in oracle rownum is not working as expected.

For mysql

SELECT * FROM TABLENAME WHERE TYPE=1 LIMIT 0,5
SELECT * FROM TABLENAME WHERE TYPE=1 LIMIT 5,10
SELECT * FROM TABLENAME WHERE TYPE=1 LIMIT 10,15

For Oracle (Not working)

SELECT * FROM TABLENAME WHERE TYPE=1 AND ROWNUM > 0 AND ROWNUM < 5
SELECT * FROM TABLENAME WHERE TYPE=1 AND ROWNUM > 5 AND ROWNUM < 10
SELECT * FROM TABLENAME WHERE TYPE=1 AND ROWNUM > 10 AND ROWNUM < 15


Any idea?

- Thanks
Zohaib.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34863
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Zohaib,
In the Oracle version, the ranges aren't sequential. You have 1-4, 6-9 and 11-14. Which would cause missing data. You need to add an = to one of the ends to get all the data.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's more complicated in Oracle, actually. Rownums are assigned only to the rows that are selected by the WHERE clause, always starting at 1. So, for example, the following query will never return any row, regardless of how many rows are there in the table:

At the same time, if no ORDER BY is given, the order of rows returned by the database may differ from call to call, and successive pagination queries might skip some rows or return some rows twice (or more times) as a result. In pagination queries, always use ORDER BY!

To sum it up: for proper pagination in Oracle, the rownums have to be generated by an inner query and then limited by the outer query:


A slightly less convoluted is pagination using ROW_NUMBER analytic function. Unlike the ROWNUM, this function always assigns numbers, even if the rows are not selected by a WHERE clause. Unfortunately, analytic functions cannot be directly used in where clauses, so an inner query is still required:

In real-world code, you'd use parameters instead of hard-coded numbers, of course (see PreparedStatement)!
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34863
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh right. I completely forgot about that! I've been using an API that does that for me.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic