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
 

 

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 Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 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 how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm


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