Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
How can i select the only 50th row from a table..

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
anil.csk

New User


Joined: 22 Oct 2007
Posts: 16
Location: Noida

PostPosted: Fri Jun 27, 2008 3:18 pm    Post subject: How can i select the only 50th row from a table..
Reply with quote

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
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Jun 27, 2008 3:37 pm    Post subject:
Reply with quote

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
View user's profile Send private message
anil.csk

New User


Joined: 22 Oct 2007
Posts: 16
Location: Noida

PostPosted: Fri Jun 27, 2008 5:39 pm    Post subject:
Reply with quote

i have to use only in db2..i am not using the cobol...
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Fri Jun 27, 2008 5:49 pm    Post subject:
Reply with quote

If you are using Spufi, limit the number of rows in defaults to 50.
Back to top
View user's profile Send private message
kregen

New User


Joined: 16 Mar 2006
Posts: 18

PostPosted: Fri Jun 27, 2008 5:49 pm    Post subject: Reply to: How can i select the only 50th row from a table..
Reply with quote

hi there
use this....

SELECT ......
FROM TABLE

FETCH FIRSt 50 ROWS ONLY

cu
kregen
Back to top
View user's profile Send private message
Manuneedhi K

Active User


Joined: 07 May 2008
Posts: 115
Location: Chennai

PostPosted: Fri Jun 27, 2008 5:56 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Manuneedhi K

Active User


Joined: 07 May 2008
Posts: 115
Location: Chennai

PostPosted: Fri Jun 27, 2008 5:59 pm    Post subject:
Reply with quote

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
View user's profile Send private message
kregen

New User


Joined: 16 Mar 2006
Posts: 18

PostPosted: Fri Jun 27, 2008 6:06 pm    Post subject:
Reply with quote

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
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Sun Jun 29, 2008 4:41 am    Post subject:
Reply with quote

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
View user's profile Send private message
surya.kalyan

New User


Joined: 09 Jan 2007
Posts: 20
Location: Mumbai

PostPosted: Tue Jul 08, 2008 11:34 am    Post subject:
Reply with quote

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
View user's profile Send private message
mohitsaini
Warnings : 1

New User


Joined: 15 May 2006
Posts: 92

PostPosted: Tue Jul 08, 2008 12:10 pm    Post subject:
Reply with quote

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
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 Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
No new posts PC (UTF-8) -> z/OS (EBCDIC) -> ... prino All Other Mainframe Topics 4 Fri Sep 01, 2017 1:47 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us