Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200

    Unanswered: First 100 records - Row Number?

    HAi all,

    I have a table that has more than 1000 records.I want to display only the first 100 records ,then the next 100 records and so on.

    In oracle there is a method with which we can select rows using row number -rownum,ie: select * from table where rownum<100;

    Is this possible in DB2 any other way to do this.

    Regards,
    micky

  2. #2
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    20
    Yes, DB2-Server has also a row_numbe()r function, we ues the following construct to retrieve some parst of the result:

    with tempselect as (select $columns, row_number() over($order) as rownum from $tables $where $grouping) select $col_new from tempselect $limit

    limit = "where rownum between " . ($offsetRef+1) . " and " . ($offsetRef + $amount);

    $col_new contains the fieldnames to select without a table prefix or the alias of a field.

    I don't know if there is an easyer way to do this.

    On an Db2-Host the ron_number() - function is only available it the OLAP-Package is installed on the DB2-Host

    Greetings Jürgen
    Last edited by hernst; 04-17-03 at 04:41.

  3. #3
    Join Date
    Mar 2003
    Posts
    33

    Re: First 100 records - Row Number?

    Here is one simple sql -command for row_number over -function:

    db2 "select * from (select col1, col2,... row_number() over() as rn from table1)xxx where rn between 1 and 100"




    Originally posted by mickykt
    HAi all,

    I have a table that has more than 1000 records.I want to display only the first 100 records ,then the next 100 records and so on.

    In oracle there is a method with which we can select rows using row number -rownum,ie: select * from table where rownum<100;

    Is this possible in DB2 any other way to do this.

    Regards,
    micky
    -r-

  4. #4
    Join Date
    Sep 2002
    Posts
    456

    Re: First 100 records - Row Number?

    You can also use this:

    select * from employee fetch first 100 rows only;

    dollar

    Originally posted by raisa
    Here is one simple sql -command for row_number over -function:

    db2 "select * from (select col1, col2,... row_number() over() as rn from table1)xxx where rn between 1 and 100"

Posting Permissions

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