How to find second row from table?

Sambhaji

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

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

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

Sambhaji · Posted: Tue Aug 26, 2008 1:45 pm Post subject:

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

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 Post subject:

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.

Sambhaji · Posted: Wed Aug 27, 2008 11:20 am Post subject:

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

Sambhaji · Posted: Wed Aug 27, 2008 11:23 am Post subject:

Name is char(4).

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

Sambhaji · Posted: Wed Aug 27, 2008 2:18 pm Post subject:

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 Post subject:

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
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.apsg/db2z_retrieverowsscrollablecursor.htm

Thanks,
Ajay