View previous topic :: View next topic
|
Author |
Message |
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 |
|
Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007 Posts: 8797 Location: Welsh Wales
|
|
|
|
Not sure, but the DISTINCT sub parameter of the SELECT command might help. |
|
Back to top |
|
|
pingte
Active User
Joined: 03 Dec 2005 Posts: 120 Location: india
|
|
|
|
Expat,
Can u please give the syntax for this?
TIA |
|
Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007 Posts: 8797 Location: Welsh Wales
|
|
|
|
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 |
|
|
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 |
|
Back to top |
|
|
UmeySan
Active Member
Joined: 22 Aug 2006 Posts: 771 Location: Germany
|
|
|
|
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 |
|
|
expat
Global Moderator
Joined: 14 Mar 2007 Posts: 8797 Location: Welsh Wales
|
|
|
|
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 |
|
Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006 Posts: 1042 Location: Richmond, Virginia
|
|
|
|
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 |
|
|
MFRASHEED
Active User
Joined: 14 Jun 2005 Posts: 186 Location: USA
|
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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
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 |
|
|
expat
Global Moderator
Joined: 14 Mar 2007 Posts: 8797 Location: Welsh Wales
|
|
|
|
Quote: |
I'm curious just how selecting the nth row would be of use? |
Dare I be cynical and suggest HOMEWORK |
|
Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006 Posts: 1042 Location: Richmond, Virginia
|
|
|
|
Why didn't I think of that? I must update my filters. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Or maybe an interview question . . . |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
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) |
|
Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010 Posts: 765 Location: Whitby, ON, Canada
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
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. |
|
Back to top |
|
|
|