Results 1 to 4 of 4

Thread: row number

  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

    Unanswered: row number

    Creating a new thread for my rownum question:


    $ db2 "select * from test"

    C1 C2
    ----------- -----------
    1 1
    2 2
    3 3
    4 4
    9 9
    5 5

    6 record(s) selected.



    I want to sort by c1 and then select row #5:


    $ db2 "select c1, c2 from (select row_number() over(order by c1) as rownum, t.* from test t) as t2 where rownum = 5"

    C1 C2
    ----------- -----------
    5 5

    1 record(s) selected.



    Is there a better way to get the same info?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What is your issue on ROW_NUMBER()?

    There are some ways to produce same results by using self join(or essentially equivalent) without ROW_NUMBER().
    But, those queries(at least I could make) are more complex and less efficient than using ROW_NUMBER().

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I just wanted to know if there a more efficient (but not complex) way of getting this info with or without using row_number() over().

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Well, with the data you gave:

    $ db2 "select c1, c2 from test t where c1 = 5"

    C1 C2
    ----------- -----------
    5 5
    Would be the most efficient way. But, I think you are after, even when your C1 column is not numbered sequentially. In that case, I think what you have is the might be the way of going about it. As I write this, causes me to think of something like this as well(completely untested):
    $ db2 "select c1, c2 from (select c1, c2 from test t order by c1 asc fetch first 5 rows only) as t order by c1 desc fetch first 1 row only"

    C1 C2
    ----------- -----------
    5 5
    Dave Nance

Posting Permissions

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