View previous topic :: View next topic
|
Author |
Message |
sureshbabu.jv
New User
Joined: 11 Apr 2006 Posts: 41 Location: Chennai
|
|
|
|
Table has 100 rows. I want to retrieve 10th row from table. Please provide a DB2 query for this
Thanks,
Suresh |
|
Back to top |
|
|
PeD
Active User
Joined: 26 Nov 2005 Posts: 459 Location: Belgium
|
|
|
|
What about reading the SELECT clause from the AVAILABLE manual SQL refernce
FETCH ONLY
Please do |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
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 |
|
|
PeD
Active User
Joined: 26 Nov 2005 Posts: 459 Location: Belgium
|
|
|
|
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 |
|
|
sureshbabu.jv
New User
Joined: 11 Apr 2006 Posts: 41 Location: Chennai
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
sureshbabu.jv
New User
Joined: 11 Apr 2006 Posts: 41 Location: Chennai
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008 Posts: 1249 Location: Richfield, MN, USA
|
|
|
|
sureshbabu.jv wrote: |
Getting the syntax problem around Row statement.. |
Please post exact message instead of telling us about the mesage. |
|
Back to top |
|
|
sureshbabu.jv
New User
Joined: 11 Apr 2006 Posts: 41 Location: Chennai
|
|
|
|
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 |
|
|
sathish_rathinam
New User
Joined: 22 Aug 2005 Posts: 59 Location: india
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
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 |
|
|
|