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
 

 

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

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Table has 100 rows. I want to retrive 10th row from table.
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: 456
Location: Belgium

PostPosted: Sun Feb 01, 2009 5:29 pm    Post subject:
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10274
Location: italy

PostPosted: Sun Feb 01, 2009 6:47 pm    Post subject: Reply to: Table has 100 rows. I want to retrive 10th row fro
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: 456
Location: Belgium

PostPosted: Sun Feb 01, 2009 7:18 pm    Post subject:
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    Post subject: Reply to: Table has 100 rows. I want to retrive 10th row fro
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

Site Director


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

PostPosted: Mon Feb 02, 2009 10:12 am    Post subject:
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    Post subject: Reply to: Table has 100 rows. I want to retrive 10th row fro
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

Site Director


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

PostPosted: Mon Feb 02, 2009 10:44 am    Post subject:
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    Post subject: Re: Reply to: Table has 100 rows. I want to retrive 10th row
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    Post subject: Reply to: Table has 100 rows. I want to retrive 10th row fro
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    Post subject: Reply to: Table has 100 rows. I want to retrive 10th row fro
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

Site Director


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

PostPosted: Mon Feb 02, 2009 11:25 am    Post subject:
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

Senior Member


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

PostPosted: Mon Feb 02, 2009 11:51 am    Post subject:
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    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 Loading data to table gives wrong for... Raghu navaikulam DB2 17 Thu Jul 13, 2017 2:11 pm
No new posts compare 2 rows within the same file ram_vizag SYNCSORT 7 Wed Jun 14, 2017 12:34 am
No new posts unload data from table with lob columns farhad_evan DB2 1 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 To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm


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