How to fetch nth row from a DB2 table.

pingte · Active User Joined: 03 Dec 2005 Posts: 120 Location: india

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

expat · Posted: Mon May 07, 2007 3:00 pm

Not sure, but the DISTINCT sub parameter of the SELECT command might help.

pingte · Active User Joined: 03 Dec 2005 Posts: 120 Location: india

Expat,

Can u please give the syntax for this?

TIA

expat · Posted: Mon May 07, 2007 3:07 pm

pingte · Active User Joined: 03 Dec 2005 Posts: 120 Location: india

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

UmeySan · Posted: Mon May 07, 2007 3:24 pm

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

expat · Posted: Mon May 07, 2007 3:27 pm

Phrzby Phil · Posted: Mon May 07, 2007 5:48 pm

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?

MFRASHEED · Active User Joined: 14 Jun 2005 Posts: 186 Location: USA

Check this previous thread and this might provide some information:

ibmmainframes.com/viewtopic.php?t=15735&highlight=

dick scherrer · Posted: Mon May 07, 2007 8:59 pm

Hello,

This

expat · Posted: Tue May 08, 2007 12:49 pm

Phrzby Phil · Posted: Tue May 08, 2007 5:58 pm

Why didn't I think of that? I must update my filters.

dick scherrer · Posted: Tue May 08, 2007 7:28 pm

Or maybe an interview question . . .

ahr · New User Joined: 24 Apr 2007 Posts: 14 Location: India

Getting N th row from a table,

I feel DB2 is an RDMS not a file.

udaybarath · New User Joined: 10 May 2005 Posts: 7

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)

don.leahy · Posted: Tue Dec 14, 2010 10:32 pm

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.

GuyC · Posted: Tue Dec 14, 2010 10:46 pm

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

bauer · New User Joined: 03 Mar 2009 Posts: 28 Location: germany

Use a scrollable Cursor.

Use FETCH ABSOLUTE <requested record> FROM MyCursor

This should work.