Professional Web Applications Themes

How to retrieve first 50 rows , next 50 rows and so on in Informix - Informix

Hi All Is there any equivalent command for SET ROWCOUNT or LIMIT in INFORMIX to get the first 50rows, next 50 rows and so on if query returns 2500 rows . So that the number of rows retrived for each page can be limited in each session. Any clues would be appreciated. Thanks in Advance Kalpana...

  1. #1

    Default How to retrieve first 50 rows , next 50 rows and so on in Informix

    Hi All

    Is there any equivalent command for SET ROWCOUNT or LIMIT in INFORMIX
    to get the first 50rows, next 50 rows and so on if query returns 2500
    rows .

    So that the number of rows retrived for each page can be limited in
    each session.

    Any clues would be appreciated.
    Thanks in Advance
    Kalpana
    KalpanaPai Guest

  2. #2

    Default Re: How to retrieve first 50 rows , next 50 rows and so on in Informix

    Hi,

    You could declare a scroll cursor.
    Using a counter you could fetch the first 50 records, doing a fetch next and
    increase the counter
    until 50 records are read.
    But it would be nicer to limit the number of selected rows.
    For example: select first 50...., select next 50...., select previous 50....
    Is this possible?

    Danny


    "KalpanaPai" <kalpanapaihotmail.com> wrote in message
    news:8b77f6f5.0308241906.21eb6f22posting.google.c om...
    > Hi All
    >
    > Is there any equivalent command for SET ROWCOUNT or LIMIT in INFORMIX
    > to get the first 50rows, next 50 rows and so on if query returns 2500
    > rows .
    >
    > So that the number of rows retrived for each page can be limited in
    > each session.
    >
    > Any clues would be appreciated.
    > Thanks in Advance
    > Kalpana

    Danny De Koster Guest

  3. #3

    Default Re: How to retrieve first 50 rows , next 50 rows and so on in Informix


    Something like:

    Select first 50 Table.* fron Table

    /Arthur

    ----- Original Message -----
    From: Danny De Koster <ddkfidelity-soft.be>
    To: <informix-listiiug.org>
    Sent: Monday, August 25, 2003 11:22 AM
    Subject: Re: How to retrieve first 50 rows , next 50 rows and so on in
    Informix

    > Hi,
    >
    > You could declare a scroll cursor.
    > Using a counter you could fetch the first 50 records, doing a fetch next
    and
    > increase the counter
    > until 50 records are read.
    > But it would be nicer to limit the number of selected rows.
    > For example: select first 50...., select next 50...., select previous
    50....
    > Is this possible?
    >
    > Danny
    >
    >
    > "KalpanaPai" <kalpanapaihotmail.com> wrote in message
    > news:8b77f6f5.0308241906.21eb6f22posting.google.c om...
    > > Hi All
    > >
    > > Is there any equivalent command for SET ROWCOUNT or LIMIT in INFORMIX
    > > to get the first 50rows, next 50 rows and so on if query returns 2500
    > > rows .
    > >
    > > So that the number of rows retrived for each page can be limited in
    > > each session.
    > >
    > > Any clues would be appreciated.
    > > Thanks in Advance
    > > Kalpana
    >
    >
    >
    sending to informix-list
    Arthur Groen Guest

  4. #4

    Default Re: How to retrieve first 50 rows , next 50 rows and so on in Informix

    I know that
    Select first 50* from Table will get only 50 rows
    But i want to know from the application side , if the query returns
    more rows , i want to get them in 50's and display and next 50 etc, in
    that way the query will not kill the memory if more sessions are
    connected without bringing the whole data to the buffer .

    Any Solution ????????/
    Thanks in advance
    Kalpana



    [email]kalpanapaihotmail.com[/email] (KalpanaPai) wrote in message news:<8b77f6f5.0308241906.21eb6f22posting.google. com>...
    > Hi All
    >
    > Is there any equivalent command for SET ROWCOUNT or LIMIT in INFORMIX
    > to get the first 50rows, next 50 rows and so on if query returns 2500
    > rows .
    >
    > So that the number of rows retrived for each page can be limited in
    > each session.
    >
    > Any clues would be appreciated.
    > Thanks in Advance
    > Kalpana
    KalpanaPai Guest

  5. #5

    Default Re: How to retrieve first 50 rows , next 50 rows and so on in Informix


    insert into temp ( as somebody already suggested ) , then fetch 50*,
    then delete first 50 and then again 50* :), or select min and max rowid
    and use an approximation like ( max - min )/50 ( divide in 50's ecah ,
    but ofcourse this set will have few rows ) and then select based on
    rowids . If u want some other order, use any numeric key .

    We used something similar in checkpointing - query will be like - where
    prod_id > l_prod_id and batch_num > l_batch_num order by prod_id, batch_num

    Rgds
    Preetinder

    KalpanaPai wrote:
    >I know that
    >Select first 50* from Table will get only 50 rows
    >But i want to know from the application side , if the query returns
    >more rows , i want to get them in 50's and display and next 50 etc, in
    >that way the query will not kill the memory if more sessions are
    >connected without bringing the whole data to the buffer .
    >
    >Any Solution ????????/
    >Thanks in advance
    >Kalpana
    >
    >
    >
    >kalpanapaihotmail.com (KalpanaPai) wrote in message news:<8b77f6f5.0308241906.21eb6f22posting.google. com>...
    >
    >
    >>Hi All
    >>
    >>Is there any equivalent command for SET ROWCOUNT or LIMIT in INFORMIX
    >>to get the first 50rows, next 50 rows and so on if query returns 2500
    >>rows .
    >>
    >>So that the number of rows retrived for each page can be limited in
    >>each session.
    >>
    >>Any clues would be appreciated.
    >>Thanks in Advance
    >>Kalpana
    >>
    >>
    sending to informix-list
    preetinder dhaliwal Guest

  6. #6

    Default Re: How to retrieve first 50 rows , next 50 rows and so on in Informix

    On 25 Aug 2003 14:27:45 -0700, [email]kalpanapaihotmail.com[/email] (KalpanaPai)
    wrote:
    >I know that
    >Select first 50* from Table will get only 50 rows
    >But i want to know from the application side , if the query returns
    >more rows , i want to get them in 50's and display and next 50 etc, in
    >that way the query will not kill the memory if more sessions are
    >connected without bringing the whole data to the buffer .
    >
    What application / development language are you using?
    John Carlson Guest

  7. #7

    Default Re: How to retrieve first 50 rows , next 50 rows and so on in Informix

    Java , java script, JSP , HTML and Webserver is Tomcat

    Thanks Heaps!!!!

    John Carlson <john_carlsonwhsmithusa.com> wrote in message news:<a7omkvoiah2g6amjo03s21rl8d29gccc8p4ax.com>. ..
    > On 25 Aug 2003 14:27:45 -0700, [email]kalpanapaihotmail.com[/email] (KalpanaPai)
    > wrote:
    >
    > >I know that
    > >Select first 50* from Table will get only 50 rows
    > >But i want to know from the application side , if the query returns
    > >more rows , i want to get them in 50's and display and next 50 etc, in
    > >that way the query will not kill the memory if more sessions are
    > >connected without bringing the whole data to the buffer .
    > >
    >
    > What application / development language are you using?
    KalpanaPai Guest

  8. #8

    Default Re: How to retrieve first 50 rows , next 50 rows and so on in Informix

    You can fetch the data like this

    for records from 0 to 50
    Select skip 0 first 50 * from table1;

    for the next set of records 50 to 100 you can use this query

    select skip 50 first 50 * from table1;


    Regards,
    Ashish
    Last edited by Ashish Singhal; September 2nd at 06:10 AM.
    Ashish Singhal is offline Junior Member
    Join Date
    Sep 2011
    Posts
    1

Similar Threads

  1. Table rows
    By miloinsydney in forum Macromedia Dynamic HTML
    Replies: 2
    Last Post: May 18th, 08:01 PM
  2. add rows
    By Mike in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: June 10th, 09:28 PM
  3. fetch first/next 20 rows
    By jane in forum Informix
    Replies: 1
    Last Post: September 2nd, 02:24 PM
  4. Select rows where other related rows don't exist
    By Dmand in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 06:23 AM
  5. Inserting into a set of rows in an SPL
    By Richard Harnden in forum Informix
    Replies: 0
    Last Post: June 26th, 09:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139