Results 1 to 7 of 7

Thread: Db2 Sql

  1. #1
    Join Date
    Jan 2004
    Posts
    25

    Unanswered: Db2 Sql

    "Select * from db2admin.NoAssetTag LIMIT " +currentRs +",10"

    Is there any similar SQL statements for the above statement in DB2?

  2. #2
    Join Date
    Feb 2004
    Posts
    5

    Yuppers

    For db2 it is:

    OPTIMIZE FOR n ROWS

    OR you can also use

    FETCH FIRST n ROWS ONLY

    So your query below would be:

    "Select * from db2admin.NoAssetTag OPTIMIZE FOR x ROWS"

  3. #3
    Join Date
    Jan 2004
    Posts
    25

    Re: Yuppers

    Originally posted by CrimsonBane
    For db2 it is:

    OPTIMIZE FOR n ROWS

    OR you can also use

    FETCH FIRST n ROWS ONLY

    So your query below would be:

    "Select * from db2admin.NoAssetTag OPTIMIZE FOR x ROWS"
    Is it possible to "Select * from db2admin.NoAssetTag OPTIMIZE FOR X,X ROWS"

    meaning for example from row 1 to 10?

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Yuppers

    I'm not sure what you mean,

    but as for the syntax, you can't have 'x.x rows'

    Cheers
    Sathyaram

    Originally posted by Shinto
    Is it possible to "Select * from db2admin.NoAssetTag OPTIMIZE FOR X,X ROWS"

    meaning for example from row 1 to 10?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jan 2004
    Posts
    25

    Re: Yuppers

    Originally posted by sathyaram_s
    I'm not sure what you mean,

    but as for the syntax, you can't have 'x.x rows'

    Cheers
    Sathyaram
    I have the following statement right now.

    Select * from db2admin.NoAssetTag OPTIMIZE FOR '"+currentRs+ ",10 ROWS'

    IS there anyway of selecting Rows between X and Y? For example. i want to select row 2 to 5 in the DB.i dont want to select everything by using the FETCH statement.
    Last edited by Shinto; 03-18-04 at 23:07.

  6. #6
    Join Date
    Feb 2004
    Posts
    5

    Nope

    No you can't, although you can do that using the WHERE clause and restricting the id field between that range. Check out the DB2 docs as they have a complete reference for valid SQL statements in DB2.

  7. #7
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    This will do what you are asking...

    Code:
    select *
    from (
            select col1
            ,        row_number() over(order by 1) rn
            from tabname
          ) table_with_row_num
    where rn between 3 and 10
    Damian

Posting Permissions

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