How to find second row from table?

Escapa · Posted: Tue Aug 26, 2008 12:17 pm

Can anybody give me SQL query to find second row in the table?

Prasanthhere · Active User Joined: 03 Aug 2005 Posts: 306

I believe there is no first row, no second row etc in a Db2. The ordering is immaterial.

Escapa · Posted: Tue Aug 26, 2008 1:45 pm

I didn't get what you are trying to explain but i will elaborate more..
What i want is
if you do select * from table it will return all the rows. I want to fetch second row from that set.

enrico-sorichetti · Posted: Tue Aug 26, 2008 2:10 pm

Your interviewer should review his understanding of db2/sql,

in a relational data base there i no inherent(positional) ordering,

asking for the second row might yield different results at different times
( You can experiment that after a reload )

dbzTHEdinosauer · Posted: Tue Aug 26, 2008 2:36 pm

at the same time, maybe the interviewer asks this question to see if the interviewee knows that this is a trick question.

otherwise, as you said Enrico, the interviewee should answer as you indicated.

Escapa · Posted: Wed Aug 27, 2008 11:20 am

Thanks. That would really help my rdbms knowledge..

But I would like to reframe Question as:

I have table like this

Name(*) Coupon_no(integer)
Abby 2122
Brad 1027
Cally 9023
Doc 2122
Mac 9278

I want to retrieve second row when it is ordered by Name

Escapa · Posted: Wed Aug 27, 2008 11:23 am

Name is char(4).

In above case query should retrieve
row with Brad as primary key.

Escapa · Posted: Wed Aug 27, 2008 2:18 pm

Can you please give me db2 query to get second row when it is ordered by name?

Ajay Baghel · Posted: Sun Aug 31, 2008 8:44 pm

You can use Scrollable cursor with FETCH ABSOLUTE in your application program for such purposes.

First declare the cursor
EXEC SQL DECLARE Cursrname INSENSITIVE SCROLL CURSOR FOR
Your select query
END-EXEC.

Open the cursor

TO fetch the 2nd row of the result table, use a FETCH statement like this:
EXEC SQL FETCH ABSOLUTE +2 Cursrname INTO :row-var END-EXEC

PS:
TO fetch the 2nd row from the end of the result table, use a FETCH statement like this:
EXEC SQL FETCH ABSOLUTE -2 Cursrname INTO :row-var END-EXEC

Please refer
publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.apsg/db2z_retrieverowsscrollablecursor.htm

Thanks,
Ajay