jQuery in Action, 3rd edition
The moose likes JDBC and Relational Databases and the fly likes using rownum with union Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "using rownum with union" Watch "using rownum with union" New topic

using rownum with union

Pranav Sharma
Ranch Hand

Joined: Oct 27, 2003
Posts: 258
In order implement pagination on the front end, I need specific number(1-10, 11-20 ..) of rows to be returned from the db.

I know we can use constraints like
select * from table Where ROWNUM < 10

However I'm using multiple (4) joins in my sql query to fetch data from oracle 10g db.

How do i achieve this.
Brian Mozhdehi
Ranch Hand

Joined: Aug 17, 2006
Posts: 81
setMaxRows(int maxRows) on statement object
Purushoth Thambu
Ranch Hand

Joined: May 24, 2003
Posts: 425
If you want to fetch for each page then you need to use ORDER BY and ROWNUM. Let's say I join 3 tables emp, dept, loc and I need n rows at a time.

The query would be

You need to bind the values for ROWNUM predicate.

- ROWNUM does not ensure that records are fetched in any particular record. If you fire a query like
SELECT * FROM emp WHERE rownum < 10
Oracle does not guarantee you will get same set of 10 row very time. The ORDER BY clause will help to overcome this unpredictable behavior.
Have you checked out Aspose?
subject: using rownum with union
It's not a secret anymore!