|
|
| Author |
Message |
anil.csk
New User
Joined: 22 Oct 2007
Posts: 15
Location: Noida
|
|
|
|
hi,
i have a table in which there are 100 fields with lacks of data.
i want to get the data of only 50th row.
how can i get that row data,, |
|
| Back to top |
|
 |
References
|
 Posted: Fri Jun 27, 2008 3:18 pm Post subject: Re: How can i select the only 50th row from a table.. |
|
|
|
|
ashimer
Senior Member
Joined: 13 Feb 2004
Posts: 348
Location: Bangalore
|
|
|
|
declare a cursor with fetch first 50 rows only .... do a dummy fetch 49 times .... the next fetch will give you the 50th row ...
pls post Db2 related stuff in DB2 forum .... |
|
| Back to top |
|
|
anil.csk
New User
Joined: 22 Oct 2007
Posts: 15
Location: Noida
|
|
|
|
| i have to use only in db2..i am not using the cobol... |
|
| Back to top |
|
|
| Moved: Fri Jun 27, 2008 5:40 pm by superk From Mainframe COBOL to DB2 |
Srihari Gonugunta
Active User
Joined: 14 Sep 2007
Posts: 164
Location: Pune
|
|
|
|
| If you are using Spufi, limit the number of rows in defaults to 50. |
|
| Back to top |
|
|
kregen
New User
Joined: 16 Mar 2006
Posts: 14
|
|
|
|
hi there
use this....
SELECT ......
FROM TABLE
FETCH FIRSt 50 ROWS ONLY
cu
kregen
|
|
| Back to top |
|
|
Manuneedhi K
Active User
Joined: 07 May 2008
Posts: 121
Location: Chennai
|
|
|
|
This should work. Please try and confirm.
| Code: |
SELECT column1 FROM table1 A1
WHERE ( 50 = (SELECT COUNT (DISTINCT (table1))
FROM table1 A2 WHERE
A2.column1<= A1.table1))
WITH UR;
|
|
|
| Back to top |
|
|
Manuneedhi K
Active User
Joined: 07 May 2008
Posts: 121
Location: Chennai
|
|
|
|
There was an error in my earlier post. Please try the one below.
| Code: |
SELECT column1 FROM table1 A1
WHERE ( 50 = (SELECT COUNT (DISTINCT (column1))
FROM table1 A2 WHERE
A2.column1<= A1.column1))
WITH UR;
|
|
|
| Back to top |
|
|
kregen
New User
Joined: 16 Mar 2006
Posts: 14
|
|
|
|
or use this
| Code: |
SELECT TB1.*,
ROW#
FROM yourtable TB1,
TABLE (SELECT COUNT(*) + 1 AS ROW#
FROM yourtable TB2
WHERE TB2.KEY < TB1.KEY) AS TEMP_TAB
WHERE ROW# = 50
FETCH FIRST 1 ROWS ONLY
;
|
cu
kregen |
|
| Back to top |
|
|
stodolas
Senior Member
Joined: 13 Jun 2007
Posts: 647
Location: Wisconsin
|
|
|
|
| There is no such concept of 50th row in DB2 unless you specify an order by, your results can not be guaranteed to be consistent. |
|
| Back to top |
|
|
surya.kalyan
New User
Joined: 09 Jan 2007
Posts: 14
Location: Mumbai
|
|
|
|
Mr Kregen,
I found your query better interms of performance (for my table ofcourse).
Did you take any special care for bettering query performance.?? |
|
| Back to top |
|
|
mohitsaini
New User
Joined: 15 May 2006
Posts: 40
|
|
|
|
| I agree with Stodolas ... There is a difference b/w a sequential file and a DB2 table. This question is more appropriate for a sequential file. |
|
| Back to top |
|
|
|
|
