Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to find second row from table?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> Mainframe Interview Questions
View previous topic :: :: View next topic  
Author Message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Tue Aug 26, 2008 12:17 pm    Post subject: How to find second row from table?
Reply with quote

Can anybody give me SQL query to find second row in the table?
Back to top
View user's profile Send private message

Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Tue Aug 26, 2008 1:09 pm    Post subject: Reply to: How to find second row from table?
Reply with quote

I believe there is no first row, no second row etc in a Db2. The ordering is immaterial.
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Tue Aug 26, 2008 1:45 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10256
Location: italy

PostPosted: Tue Aug 26, 2008 2:10 pm    Post subject: Reply to: How to find second row from table?
Reply with quote

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 )
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Tue Aug 26, 2008 2:36 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Wed Aug 27, 2008 11:20 am    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Wed Aug 27, 2008 11:23 am    Post subject:
Reply with quote

Name is char(4).

In above case query should retrieve
row with Brad as primary key.
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Wed Aug 27, 2008 2:18 pm    Post subject:
Reply with quote

Can you please give me db2 query to get second row when it is ordered by name?
Back to top
View user's profile Send private message
Ajay Baghel

Active User


Joined: 25 Apr 2007
Posts: 205
Location: Bangalore

PostPosted: Sun Aug 31, 2008 8:44 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> Mainframe Interview Questions All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts find RACF group for access to spooled... jzhardy JCL & VSAM 1 Mon May 08, 2017 11:46 am
No new posts To find out size allocated to a seque... ashek15 JCL & VSAM 15 Thu Apr 27, 2017 9:42 am
No new posts unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us