SELECT returns a range of records?

Discussion in 'Java' started by Ahmed Moustafa, Jul 20, 2003.

  1. 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
     
    Ahmed Moustafa, Jul 20, 2003
    #1
    1. Advertising

  2. Ahmed Moustafa

    Roedy Green Guest

    On Sun, 20 Jul 2003 01:24:39 GMT, Ahmed Moustafa <>
    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.
     
    Roedy Green, Jul 20, 2003
    #2
    1. Advertising

  3. 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.
     
    Ahmed Moustafa, Jul 20, 2003
    #3
  4. 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:
    PGP-encrypted mails preferred (Key-ID: 0x8BC3CD81)

    Always remember: The answer is forty-two, there can only be wrong
    questions!
     
    Lothar Kimmeringer, Jul 20, 2003
    #4
  5. Ahmed Moustafa

    winbatch Guest

    Don't know about DB2, but Oracle is 'where rownum >=10 and rownum <=20'.


    "Ahmed Moustafa" <> wrote in message
    news:rbmSa.110228$...
    > 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
    >
     
    winbatch, Jul 20, 2003
    #5
  6. It seems DB2 does not have Oracle's "ROWNUM" not only that but JDBC does
    not like DB2's "FETCH" :(
     
    Ahmed Moustafa, Jul 21, 2003
    #6
  7. 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" <> schrieb im Newsbeitrag
    news:rbmSa.110228$...
    > 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
    >
     
    Joachim Müller, Jul 21, 2003
    #7
  8. Ahmed Moustafa

    Jason Guest

    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 <> wrote in message news:<rbmSa.110228$>...
    > 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
     
    Jason, Jul 21, 2003
    #8
  9. Thanks!!!
     
    Ahmed Moustafa, Jul 22, 2003
    #9
  10. 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.
     
    Ahmed Moustafa, Jul 24, 2003
    #10
  11. 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.
     
    Ahmed Moustafa, Jul 24, 2003
    #11
  12. Ahmed Moustafa

    winbatch Guest

    You are using DB2, right? Check this out...
    http://www7b.boulder.ibm.com/dmdd/library/techarticle/adamache/0430_adamache3.html

    "Ahmed Moustafa" <> wrote in message
    news:...
    > 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.
    >
     
    winbatch, Jul 24, 2003
    #12
  13. Read this article, spefically the sections on rownum and fetch first:

    http://www7b.boulder.ibm.com/dmdd/library/techarticle/adamache/0430_adamache3.html

    SELECT * FROM (SELECT NAME, rownumber() OVER
    (ORDER BY NAME)
    AS rn FROM ADDRESS)
    AS tr WHERE rn BETWEEN 10 and 20

    Ahmed Moustafa wrote:

    > 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
    >
     
    Blair Adamache, Jul 24, 2003
    #13
  14. Ahmed Moustafa

    soft-eng Guest

    Ahmed Moustafa <> wrote in message news:<>...
    > 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.


    Databases don't actually execute statements the way you think; typically
    such a statement would be read in its entirety, optimized, a "plan"
    made, and then the data would be retrieved. So as long as you
    can tell the database clearly what you want, it will do that
    in some sort of optimized manner.

    Of course, this can vary from vendor to vendor. But IBM
    has been in the database business for a while...

    The best thing to do is to run some performance tests.
     
    soft-eng, Jul 24, 2003
    #14
  15. Ahmed Moustafa

    Steve Guest

    What has this got to do with Java? Please stop X-posting on to
    c.l.java.programmer.

    Thanks.


    On Sun, 20 Jul 2003 01:24:39 GMT, Ahmed Moustafa <>
    wrote:

    >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



    ~ If emailing, please use: Steve_A_Haigh
    ~ @
    ~ hotmail.com
    ~
     
    Steve, Jul 25, 2003
    #15
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Luke Airig
    Replies:
    0
    Views:
    796
    Luke Airig
    Dec 31, 2003
  2. k3xji
    Replies:
    5
    Views:
    724
    Steven D'Aprano
    Sep 16, 2010
  3. Dan

    Delete records or update records

    Dan, May 10, 2004, in forum: ASP General
    Replies:
    1
    Views:
    467
    Ray at
    May 10, 2004
  4. Replies:
    3
    Views:
    668
    Anthony Jones
    Nov 2, 2006
  5. Srijayanth Sridhar
    Replies:
    19
    Views:
    627
    David A. Black
    Jul 2, 2008
Loading...

Share This Page