IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Table has 100 rows. I want to retrive 10th row from table.


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sureshbabu.jv

New User


Joined: 11 Apr 2006
Posts: 41
Location: Chennai

PostPosted: Sun Feb 01, 2009 5:12 pm
Reply with quote

Table has 100 rows. I want to retrieve 10th row from table. Please provide a DB2 query for this

Thanks,
Suresh
Back to top
View user's profile Send private message
PeD

Active User


Joined: 26 Nov 2005
Posts: 459
Location: Belgium

PostPosted: Sun Feb 01, 2009 5:29 pm
Reply with quote

What about reading the SELECT clause from the AVAILABLE manual SQL refernce

FETCH ONLY

Please do
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Sun Feb 01, 2009 6:47 pm
Reply with quote

Please review Your understanding of DB2...
in a relational data base system there is no nth row/record,
furthermore, without an order by clause, the row retrieval/presentation order will be unpredictable
Back to top
View user's profile Send private message
PeD

Active User


Joined: 26 Nov 2005
Posts: 459
Location: Belgium

PostPosted: Sun Feb 01, 2009 7:18 pm
Reply with quote

Enrico,

I can understand that some times you want to have a sample of your data stored in DB2 table to be able to analyse their content, yo pursue with the build of a specific application, to prepare some tests cases , etc etc

But yes, DB2 tables and files are different approach.

Cheers
Pierre
Back to top
View user's profile Send private message
sureshbabu.jv

New User


Joined: 11 Apr 2006
Posts: 41
Location: Chennai

PostPosted: Mon Feb 02, 2009 10:06 am
Reply with quote

I have written a below query for my request. As per my request applied ORDER by clause on PR_NUMBER and retrieving the data between 3 to 6 row. The below query is not working. COuld you suggest me what changes have to be done in the below to meet my requirement.

Code:
SELECT * FROM (SELECT PR_number
,Vendor_name
,ROW_NUMBER() OVER(ORDER BY PR_number DSC) AS R 
FROM ACCOUNT
)AS ACCOUNT
WHERE R BETWEEN 3 AND 6
ORDER BY PR_NUMBER;

Thanks,
Suresh

Edited: Please use BBcode when You post some code/error, that's rather readable, Thanks... Anuj
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Mon Feb 02, 2009 10:12 am
Reply with quote

Hello,

Quote:
The below query is not working
If you want someone to help, you need to specify what is wrong. Telling us "it didn't work" just wastes everyone's time.

Did you get an error? Did you get an unexpected/undesired result?

You need to post the data and the expexted result from that data. If you query did not return what you expected, you additionally need to post the "bad" result.
Back to top
View user's profile Send private message
sureshbabu.jv

New User


Joined: 11 Apr 2006
Posts: 41
Location: Chennai

PostPosted: Mon Feb 02, 2009 10:28 am
Reply with quote

Getting the syntax problem around Row statement.. Just want to know whether that query will meet my requirement or not.. If you have any model query which can use to retrive 10 th row, will apply ORDER clause to get row data..

Thanks for your help!

Suresh
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Mon Feb 02, 2009 10:44 am
Reply with quote

Hello,

Quote:
Just want to know whether that query will meet my requirement or not.. If you have any model query which can use to retrive 10 th row
Probably not. . .
Quote:
retrieving the data between 3 to 6 row
Before a query might be written, there needs to be single, accurate definition of the requirement.

Another problem is what has already been mentioned - a table does not have an "nth" row.

Quote:
will apply ORDER clause to get row data
What might you ORDER?

Please post something from the sql reference that shows where you found the syntax you have posted.
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Mon Feb 02, 2009 10:56 am
Reply with quote

sureshbabu.jv wrote:
Getting the syntax problem around Row statement..
Please post exact message instead of telling us about the mesage.
Back to top
View user's profile Send private message
sureshbabu.jv

New User


Joined: 11 Apr 2006
Posts: 41
Location: Chennai

PostPosted: Mon Feb 02, 2009 10:59 am
Reply with quote

I have to provided 2 requirements,it can be 10th row or 3 to 6th rows.Want to have query to use for this..

Please look at these queries..

I have found the below 2 queries from DB2 cook book.

Figure 167, Select first 3 rows, using FETCH FIRST notation
So far, the ROW_NUMBER and the FIRST FETCH notations seem to be about the same. But
the former technique is much more flexible. To illustrate, in the next query we retrieve the 3rd
through 6th matching rows:

SELECT *
FROM (SELECT ID,NAME ID
,ROW_NUMBER() OVER(ORDER BY ID) AS R
FROM STAFF
WHERE ID < 200
AND YEARS IS NOT NULL
)AS XXX
WHERE R BETWEEN 3 AND 6
ORDER BY ID;


Figure 168, Select 3rd through 6th rows

In the next query we get every 5th matching row - starting with the first:

SELECT *
FROM (SELECT ID
,NAME
,ROW_NUMBER() OVER(ORDER BY ID) AS R
FROM STAFF
WHERE ID < 200
AND YEARS IS NOT NULL
)AS XXX
WHERE (R - 1) = ((R - 1) / 5) * 5
ORDER BY ID;
Back to top
View user's profile Send private message
sathish_rathinam

New User


Joined: 22 Aug 2005
Posts: 59
Location: india

PostPosted: Mon Feb 02, 2009 11:07 am
Reply with quote

Use a Sql query like below in program and process it in a cursor


EXEC SQL FETCH ABSOLUTE (+10) C1 INTO :HOST STRUCTURE
END-EXEC
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Mon Feb 02, 2009 11:25 am
Reply with quote

Hello,

Quote:
I have to provided 2 requirements,
I believe not - those are not requirements. . . You have posted 2 things you found somewhere (db2 cookbook?) and are weaving them thru a single topic.

As you were told earlier, post the real data from the table and what you expect from one query. It makes no sense to ping-pong between them.

Quote:
EXEC SQL FETCH ABSOLUTE (+10) C1 INTO :HOST STRUCTURE
END-EXEC
You could declare a cursor and use a fetch like this, but what was fetched would be questionable for any business use. . .
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Mon Feb 02, 2009 11:51 am
Reply with quote

Hi,

What's been suggested so far about the relational Databases is right (however I'm not getting what your requirement is all about) - but if I go with the subject title of this thread, you can try something like, however you need to pay attention what's been told by Dick & Enrico:
Code:
SELECT * FROM Table.name
ORDER BY some_column_name ASC
FETCH FIRST 10 ROWS ONLY   
& then use the value of last or first row as per your need.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Multiple table unload using INZUTILB DB2 2
No new posts Check data with Exception Table DB2 0
Search our Forums:

Back to Top