File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes mysql limit and oracle rownum to limit resultset Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "mysql limit and oracle rownum to limit resultset" Watch "mysql limit and oracle rownum to limit resultset" New topic
Author

mysql limit and oracle rownum to limit resultset

zohaib khan
Greenhorn

Joined: Apr 13, 2009
Posts: 22
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

Joined: May 26, 2003
Posts: 31079
    
163

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

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

Joined: May 26, 2003
Posts: 31079
    
163

Oh right. I completely forgot about that! I've been using an API that does that for me.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: mysql limit and oracle rownum to limit resultset