Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > SELECT returns a range of records?

Reply
Thread Tools

SELECT returns a range of records?

 
 
Ahmed Moustafa
Guest
Posts: n/a
 
      07-20-2003
Hi All,

How can a SELECT statement SQL return a certain range of the records of
the result set e.g. say the total number of records is 100 records and I
am interested only the records from 10 to 20?

DB: DB2 UDB 7.0
OS: AIX

Thanks in advance,
Ahmed

 
Reply With Quote
 
 
 
 
Roedy Green
Guest
Posts: n/a
 
      07-20-2003
On Sun, 20 Jul 2003 01:24:39 GMT, Ahmed Moustafa <(E-Mail Removed)>
wrote or quoted :

>say the total number of records is 100 records and I
>am interested only the records from 10 to 20?


Either you figure out a way to describe your query to prune down what
you want, or you wade through result records 1 to 9, read 10 to 20
then dismiss the result set. Don't feel guilty. The database does
not actually compose the entire result set when you submit your query.

--
Canadian Mind Products, Roedy Green.
Coaching, problem solving, economical contract programming.
See http://mindprod.com/jgloss/jgloss.html for The Java Glossary.
 
Reply With Quote
 
 
 
 
Ahmed Moustafa
Guest
Posts: n/a
 
      07-20-2003
Roedy Green wrote:
>>say the total number of records is 100 records and I
>>am interested only the records from 10 to 20?

>
>
> Either you figure out a way to describe your query to prune down what
> you want, or you wade through result records 1 to 9, read 10 to 20
> then dismiss the result set. Don't feel guilty. The database does
> not actually compose the entire result set when you submit your query.


I remember it was possible in Oracle to qualify the query by the record
number, I am looking for something equivalent in DB2 and the only
objective is to improve the performance of the query.

 
Reply With Quote
 
Lothar Kimmeringer
Guest
Posts: n/a
 
      07-20-2003
On Sun, 20 Jul 2003 01:24:39 GMT, Ahmed Moustafa wrote:

> How can a SELECT statement SQL return a certain range of the records of
> the result set e.g. say the total number of records is 100 records and I
> am interested only the records from 10 to 20?


LIMIT should be the statement of your choice. How it is used
you should be able to find in your database-manual.


Regards, Lothar
--
Lothar Kimmeringer E-Mail: http://www.velocityreviews.com/forums/(E-Mail Removed)
PGP-encrypted mails preferred (Key-ID: 0x8BC3CD81)

Always remember: The answer is forty-two, there can only be wrong
questions!
 
Reply With Quote
 
winbatch
Guest
Posts: n/a
 
      07-20-2003
Don't know about DB2, but Oracle is 'where rownum >=10 and rownum <=20'.


"Ahmed Moustafa" <(E-Mail Removed)> wrote in message
news:rbmSa.110228$(E-Mail Removed) rthlink.net...
> Hi All,
>
> How can a SELECT statement SQL return a certain range of the records of
> the result set e.g. say the total number of records is 100 records and I
> am interested only the records from 10 to 20?
>
> DB: DB2 UDB 7.0
> OS: AIX
>
> Thanks in advance,
> Ahmed
>



 
Reply With Quote
 
Ahmed Moustafa
Guest
Posts: n/a
 
      07-20-2003
It seems DB2 does not have Oracle's "ROWNUM" not only that but JDBC does
not like DB2's "FETCH"

 
Reply With Quote
 
Joachim Müller
Guest
Posts: n/a
 
      07-21-2003
Ahmed,

this works...

get rows from 10 to 20

row number function:

SELECT * FROM
(SELECT name, rownumber() over
(order by name)
AS rn FROM address)
AS tr WHERE rn between 10 and 20

Regards, Joachim

"Ahmed Moustafa" <(E-Mail Removed)> schrieb im Newsbeitrag
news:rbmSa.110228$(E-Mail Removed) rthlink.net...
> Hi All,
>
> How can a SELECT statement SQL return a certain range of the records of
> the result set e.g. say the total number of records is 100 records and I
> am interested only the records from 10 to 20?
>
> DB: DB2 UDB 7.0
> OS: AIX
>
> Thanks in advance,
> Ahmed
>



 
Reply With Quote
 
Jason
Guest
Posts: n/a
 
      07-21-2003
Depends on where you want to control it and how reliable you feel
about it being in the first 10-20 records, or if what you're really
after is paging behavior in which case things change a bit.

Assumptions (Potential hazard) : You're running server side java in a
browser implementation wherein, your interface (jsp) elements are
displayed in a browser and you want the operational (behavioral) code
on the server side. Basic web implementation type of thing. If
you're doing a desktop swing app or similar your mileage will vary.

One thing I've done in similar situations was to actually bring back
all of the primary keys I might need, for example, search returns a
result of 1000 records, but I'm only getting the primary keys and
maybe a descriptor. I put ALL of the records into objects which get
stored in a wrapper class that has the behavior for paging the 1000
records in pages of say 25. The wrapper class has to be able to keep
up with the page size and the current page which yields an index range
of objects in the collection that will allow you to perform paging
behavior without having to A) go back to the database over and over
again. (You actually will go back to the database, but not to get
this list or any part of it) and B) to more efficiently manage how
much information your pulling from the database at any given time.

In my experience, RDBMS systems and networks do not like moving large
blocks of information, they'll do it, but they're happier moving lots
of little bits of information. This approach allows you to do that.
It's semantically a different way of thinking about doing data
exchange.

If you're interested I have a wealth of information available and
would be happy to share. Feel free to email me.

Ahmed Moustafa <(E-Mail Removed)> wrote in message news:<rbmSa.110228$(E-Mail Removed) arthlink.net>...
> Hi All,
>
> How can a SELECT statement SQL return a certain range of the records of
> the result set e.g. say the total number of records is 100 records and I
> am interested only the records from 10 to 20?
>
> DB: DB2 UDB 7.0
> OS: AIX
>
> Thanks in advance,
> Ahmed

 
Reply With Quote
 
Ahmed Moustafa
Guest
Posts: n/a
 
      07-22-2003
Thanks!!!

 
Reply With Quote
 
Ahmed Moustafa
Guest
Posts: n/a
 
      07-24-2003
Joachim Müller wrote:
> Ahmed,
>
> this works...
>
> get rows from 10 to 20
>
> row number function:
>
> SELECT * FROM
> (SELECT name, rownumber() over
> (order by name)
> AS rn FROM address)
> AS tr WHERE rn between 10 and 20
>
> Regards, Joachim


It works perfectly fine BUT performance-wise, it does not improve the
performance, does it? (The inner SELECT still returns the whole result set.)

My main objective is to improve the performance of that query. My
understanding is that one of the parameters of the response time is the
number of records in the result set so I am looking for retrieving only
the records that I was interested in to improve the performance.

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
select() call and filedescriptor out of range in select error k3xji Python 5 09-16-2010 07:14 AM
Sorted Returns List and Reversed Returns Iterator ++imanshu Python 7 08-23-2008 04:25 AM
Hash#select returns an array but Hash#reject returns a hash... Srijayanth Sridhar Ruby 19 07-02-2008 12:49 PM
createImage sometime returns null and sometime returns non-null. vizlab Java 3 10-17-2007 11:21 AM
select of select box will select multiple in another box palmiere Javascript 1 02-09-2004 01:11 PM



Advertisments