Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> How do I "page" result sets???

How do I "page" result sets???

From: Gopal Santhanam <gopal_at_bisc.EECS.Berkeley.EDU>
Date: 15 Dec 1999 16:13:00 GMT
Message-ID: <838emc$fat$1@agate.berkeley.edu>


Hi,

I am writing a web application that uses JDBC to communicate between the application layer and the database. Each external user of my application has several rows worth of user data. I want to show this data to the user in pages of 10 rows each. The user is allowed to sort his view by a particular column. The sort will be applied to his entire result set, while he still pages through each set of 10. This is pretty standard stuff and is implemented at many sites. Yahoo Auto Classifieds is a good example.

So, the best way I could think of doing this was to issue the following query to the database:

select * from (select rownum my_rownum, <fields> from

               (select <fields> from <tables> where a = b order by c))     where my_rownum between 11 AND 20;

I have to use one nested select because the "order by" clause doesn't affect the row numbering! I have to issue the other nested select because apparently you can't do a "where" condition on rownum until all the rows have been numbered.

This will work but seems pretty ugly and is a dramatic special case compared to the other queries I execute in the application layer. Is there a better way? Maybe I can use some SQL function wisely or stored procedure?

If this is the only way, how could I abstract this away into a stored procedure and hide some of this complexity?

Any help would be greatly appreciated.

Thanks in advance!
Gopal Received on Wed Dec 15 1999 - 10:13:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US