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 fetch nth row from a DB2 table.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
pingte

Active User


Joined: 03 Dec 2005
Posts: 120
Location: india

PostPosted: Mon May 07, 2007 2:48 pm    Post subject: How to fetch nth row from a DB2 table.
Reply with quote

Hi,

I want to fetch nth row (like 7th or 10th row) directly from a DB2 table.
I don't want to sequentially access the previous (n-1) rows.

Is there any concept like ROWID in DB2 by which this can be acheived.

Please let me know how to go about this. Also please give the syntax.

Thanks in advance
Back to top
View user's profile Send private message

expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Mon May 07, 2007 3:00 pm    Post subject:
Reply with quote

Not sure, but the DISTINCT sub parameter of the SELECT command might help.
Back to top
View user's profile Send private message
pingte

Active User


Joined: 03 Dec 2005
Posts: 120
Location: india

PostPosted: Mon May 07, 2007 3:02 pm    Post subject:
Reply with quote

Expat,

Can u please give the syntax for this?

TIA
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Mon May 07, 2007 3:07 pm    Post subject:
Reply with quote

Quote:
Can u please give the syntax for this?

Of course, It can be found it in the SQL manual, by clicking the "Manuals" button at the top of this page. That's where I found it.
Back to top
View user's profile Send private message
pingte

Active User


Joined: 03 Dec 2005
Posts: 120
Location: india

PostPosted: Mon May 07, 2007 3:18 pm    Post subject:
Reply with quote

Expat,

I saw the manual.
DISTINCT is used to eliminate the duplicate row.

My question is how can i directly fetch the 7th row of a table?

TIA
Back to top
View user's profile Send private message
UmeySan

Active Member


Joined: 22 Aug 2006
Posts: 743
Location: Germany

PostPosted: Mon May 07, 2007 3:24 pm    Post subject:
Reply with quote

Hi pingte !

No way, no chance. There's nothing like your ROWID ore something else.
If you want somthing like that, you have to implement your own serial number as a field in your table.

DISTINCT is only used to eliminate duplicate rows.

So you have to fetch and count.

Regards, UmeySan
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Mon May 07, 2007 3:27 pm    Post subject:
Reply with quote

Quote:
DISTINCT is only used to eliminate duplicate rows.

Dang, I used it in MS Access to get only the last row from a table, but perhaps a vastly different version of SQL sterb050.gif
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 961
Location: Richmond, Virginia

PostPosted: Mon May 07, 2007 5:48 pm    Post subject:
Reply with quote

Unless you are traversing via in index, the record order should be immaterial to your processing and transparent to the user. It could even be changed (by a reorg or reload) and your processing should not be affected.

Why do you want "the nth" row? Just the nth, or every nth? For testing or a real functional requirement?
Back to top
View user's profile Send private message
MFRASHEED

Active User


Joined: 14 Jun 2005
Posts: 186
Location: USA

PostPosted: Mon May 07, 2007 8:17 pm    Post subject:
Reply with quote

Check this previous thread and this might provide some information:

http://ibmmainframes.com/viewtopic.php?t=15735&highlight=
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon May 07, 2007 8:59 pm    Post subject:
Reply with quote

Hello,

This
Quote:
I used it in MS Access to get only the last row from a table
might get the last row if everything in the predicate was the same value icon_confused.gif

I'm curious just how selecting the nth row would be of use? As rows are added/deleted, row 3087 may be a different row from day-to-day or even minute-to-minute. . . If this is a readonly table that is re-created periodically, maybe, but it seems like a stretch for most processing. . .
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Tue May 08, 2007 12:49 pm    Post subject:
Reply with quote

Quote:
I'm curious just how selecting the nth row would be of use?

Dare I be cynical and suggest HOMEWORK
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 961
Location: Richmond, Virginia

PostPosted: Tue May 08, 2007 5:58 pm    Post subject:
Reply with quote

Why didn't I think of that? I must update my filters.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue May 08, 2007 7:28 pm    Post subject:
Reply with quote

Or maybe an interview question . . .
Back to top
View user's profile Send private message
ahr

New User


Joined: 24 Apr 2007
Posts: 14
Location: India

PostPosted: Tue May 15, 2007 5:14 pm    Post subject:
Reply with quote

Getting N th row from a table,

I feel DB2 is an RDMS not a file.
Back to top
View user's profile Send private message
udaybarath

New User


Joined: 10 May 2005
Posts: 7

PostPosted: Tue Dec 14, 2010 8:46 pm    Post subject: Reply to: How to fetch nth row from a DB2 table.
Reply with quote

Please correct me if I am wrong:

Select * from Table_name A
Where N = (select count(primary_key_column) from
Table_name B WHERE
A. Primary_key_column <= B.Primary_key_column)
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 641
Location: Whitby, ON, Canada

PostPosted: Tue Dec 14, 2010 10:32 pm    Post subject:
Reply with quote

There is FETCH FROM C1 BEFORE ABSOLUTE :hv , but I have never tried it because I've never had the need.

The details are "hidden" in an obscure source called the SQL Reference manual. See the section dealing with the FETCH statement.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Dec 14, 2010 10:46 pm    Post subject:
Reply with quote

There is no such thing as accessing the Nth row directly.

1) you have to specify an order. without an order , you don't have a notion of "the Nth"
2) DB2 needs to count, so the previous rows(pointers) are at least touched.

Having said that, check out ROW_NUMBER() in DB2 v9
Back to top
View user's profile Send private message
bauer

New User


Joined: 03 Mar 2009
Posts: 26
Location: germany

PostPosted: Thu Dec 16, 2010 2:27 pm    Post subject:
Reply with quote

Use a scrollable Cursor.

Use FETCH ABSOLUTE <requested record> FROM MyCursor

This should work.
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 -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Multi row fetch - "for read only... Nileshkul DB2 3 Sun Aug 14, 2016 12:52 am
No new posts Updating online Db2 table kishpra DB2 3 Fri Aug 12, 2016 2:44 pm
No new posts JCL to fetch schedule status from act... parasmalik20 CA Products 1 Thu Jun 02, 2016 7:11 pm


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