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?

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().

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().

Well, with the data you gave:

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):
Dave Nance