Professional Web Applications Themes

DB2 Select - how to return a range of results - IBM DB2

Hi, Here is some SQL from MS SQL 2000: select top 5 * from (select top 5 * from (select top 25 code, name, address from contacts where name like '%Bob%' order by code asc) as a1 order by code desc) as a2 order by code asc The objective is to return results say 20 - 25 from a result set of 50 to display on a web page. Range to return would be passed as parameters. How is this accomplished in DB2 6.1 since DB2 doesn't appear to support the TOP command?? Can a SQL guru assist me? Cheers. ...

  1. #1

    Default DB2 Select - how to return a range of results

    Hi,

    Here is some SQL from MS SQL 2000:

    select top 5 * from
    (select top 5 * from
    (select top 25 code, name, address from contacts where name like
    '%Bob%' order by code asc) as a1
    order by code desc) as a2
    order by code asc

    The objective is to return results say 20 - 25 from a result set of 50
    to display on a web page. Range to return would be passed as
    parameters.

    How is this accomplished in DB2 6.1 since DB2 doesn't appear to
    support the TOP command??

    Can a SQL guru assist me?

    Cheers.


    Surfing the information superhighway in style at Warp speed :-)
    Igor Pool Guest

  2. #2

    Default Re: DB2 Select - how to return a range of results

    Igor Pool <igorigor.co.nz> wrote:
    > Hi,
    >
    > Here is some SQL from MS SQL 2000:
    >
    > select top 5 * from
    > (select top 5 * from
    > (select top 25 code, name, address from contacts where name like
    > '%Bob%' order by code asc) as a1
    > order by code desc) as a2
    > order by code asc
    >
    > The objective is to return results say 20 - 25 from a result set of 50
    > to display on a web page. Range to return would be passed as
    > parameters.
    >
    > How is this accomplished in DB2 6.1 since DB2 doesn't appear to
    > support the TOP command??
    Use the row_number() function to associate an ever-increasing number with
    each row and then select only those rows you want:

    SELECT code, name, address
    FROM ( SELECT row_number() OVER ( ORDER BY code ) AS rid,
    code, name, address
    FROM contacts
    WHERE name LIKE '%Bob%' ) AS t
    WHERE t.rid BETWEEN 20 AND 25;

    Alternatively, you can use recursive SQL or the counter UDF (see samples).

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

  3. #3

    Default Re: DB2 Select - how to return a range of results

    For general education the literal translation (the OLAP proposals are
    better in thsi case IMHO):

    select * from
    (select * from
    (select code, name, address from contacts where name like '%Bob%'
    order by code asc fetch first 25 rows only) as a1
    order by code desc fetch first 5 rows only) as a2
    order by code asc fetch first 5 rows only

    I do not think the original SQL Server query did what you wanted it to.
    Teh outer top 5 will not do anything to you given the inner top 5 (same
    with FF5RO).

    Cheers
    Serge

    --
    Serge Rielau
    DB2 UDB SQL Compiler Development
    IBM Software Lab, Toronto

    Visit DB2 Developer Domain at
    [url]http://www7b.software.ibm.com/dmdd/[/url]

    Serge Rielau Guest

Similar Threads

  1. Select a range around a specific row
    By Wild Bill Hiccup in forum MySQL
    Replies: 7
    Last Post: April 20th, 07:02 PM
  2. need to return results from dynamic quizes
    By AFstudios in forum Macromedia Flash Actionscript
    Replies: 0
    Last Post: February 12th, 07:26 AM
  3. Trying to select range for Excel chart
    By hansioux in forum PERL Miscellaneous
    Replies: 2
    Last Post: August 6th, 06:43 PM
  4. Return Search Results on the Same Page
    By shell in forum ASP Database
    Replies: 2
    Last Post: July 14th, 11:27 AM
  5. Select Top - percent range?
    By Becky in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 7th, 09: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