Professional Web Applications Themes

Selecting rows in ranges - IBM DB2

What is the fastest way to select rows x to x in a table using db2? Following is the query to do this in Oracle. What is the equivalent in db2? ================================================== ================ SELECT * FROM (select inner.*, rownum rnum from (SELECT s.tableA_id xyz0, s.crtd_dtt xyz1, s.tableA_id xyz2, s.tableA_id xyz3, s.tableA_num xyz4, s.tableA_desc xyz5, s.cur_optlstat_id xyz6, s.display_status xyz7 FROM tableA_t s WHERE 1 = 1 AND s.tableA_id > '65185' AND s.display_status = 5005 order by s.crtd_dtt desc, s.tableA_id) inner where rownum < 100) WHERE rnum >= 37 ================================================== ================ Thanks, -Jane...

  1. #1

    Default Selecting rows in ranges

    What is the fastest way to select rows x to x in a table using db2?

    Following is the query to do this in Oracle. What is the equivalent in db2?
    ================================================== ================
    SELECT *
    FROM
    (select inner.*, rownum rnum
    from
    (SELECT s.tableA_id xyz0, s.crtd_dtt xyz1,
    s.tableA_id xyz2, s.tableA_id
    xyz3, s.tableA_num xyz4, s.tableA_desc xyz5,
    s.cur_optlstat_id xyz6, s.display_status xyz7 FROM tableA_t s
    WHERE 1 = 1
    AND s.tableA_id > '65185'
    AND s.display_status = 5005
    order by s.crtd_dtt desc, s.tableA_id) inner
    where rownum < 100)
    WHERE rnum >= 37
    ================================================== ================
    Thanks,
    -Jane
    Jane Guest

  2. #2

    Default Re: Selecting rows in ranges

    Jane <jane_estradai2.com> wrote:
    > What is the fastest way to select rows x to x in a table using db2?
    >
    > Following is the query to do this in Oracle. What is the equivalent in
    > db2? ================================================== ================
    > SELECT *
    > FROM
    > (select inner.*, rownum rnum
    > from
    > (SELECT s.tableA_id xyz0, s.crtd_dtt xyz1,
    > s.tableA_id xyz2, s.tableA_id
    > xyz3, s.tableA_num xyz4, s.tableA_desc xyz5,
    > s.cur_optlstat_id xyz6, s.display_status xyz7 FROM
    > tableA_t s
    > WHERE 1 = 1
    > AND s.tableA_id > '65185'
    > AND s.display_status = 5005
    > order by s.crtd_dtt desc, s.tableA_id) inner
    > where rownum < 100)
    > WHERE rnum >= 37
    > ================================================== ================
    I would do it like this:

    SELECT *
    FROM ( SELECT ...,
    row_number() over(order by s.crtd_dtt desc, s.tableA_id)
    FROM tableA_t s
    WHERE s.tableA_id > '65185' AND
    s.display_status = 5005 ) AS inner(..., rnum)
    WHERE rnum BETWEEN 37 AND 99

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Stolze Guest

  3. #3

    Default Re: Selecting rows in ranges

    Knut,

    Thanks for the response. Does this type of query perform well? We
    have Web application and we are going to use this type of query to
    allow users to jump pages. The tables will have over 5 million
    records.

    Thanks,
    -Jane
    Jane Guest

  4. #4

    Default Re: Selecting rows in ranges

    "Jane" <jane_estradai2.com> wrote in message
    news:75f068bb.0309111618.507a20cdposting.google.c om...
    > Knut,
    >
    > Thanks for the response. Does this type of query perform well? We
    > have Web application and we are going to use this type of query to
    > allow users to jump pages. The tables will have over 5 million
    > records.
    >
    > Thanks,
    > -Jane
    Just for starters, you need to explain what indexes you have on the table(s)
    accessed before and estimate of query performance can be determined.


    Mark A Guest

Similar Threads

  1. selecting based on matches on multiple rows?
    By Tim Smith in forum MySQL
    Replies: 1
    Last Post: May 31st, 01:32 AM
  2. Selecting Multiple Rows from DB
    By Ultrashock in forum Coldfusion Database Access
    Replies: 7
    Last Post: July 18th, 03:36 AM
  3. Selecting rows not visible in DataGrid
    By globalview in forum Macromedia Flex General Discussion
    Replies: 2
    Last Post: May 5th, 03:45 PM
  4. SELECTING ROWS
    By MacroDuck in forum Coldfusion Database Access
    Replies: 2
    Last Post: April 20th, 06:58 PM
  5. DataGrid: Selecting multiple rows/columns
    By PontiMax in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: January 12th, 12:38 PM

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