IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

How to retrieve mutliple current rows


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
gopurs

New User


Joined: 04 Apr 2005
Posts: 13

PostPosted: Fri Apr 22, 2005 1:10 pm
Reply with quote

Hi,

Please see the below data in a table. I would like to retrieve only those records from the table where there are multiple current_row value of '1'.
Can anyone suggest the query for retrieving the same in DB2.

Ref current_row

ABCD 0
ABCD 0
ABCD 1
ABCD 1

DEFG 0
DEFG 0
DEFG 0
DEFG 0
DEFG 0


MANR 0
MANR 0
MANR 0
MANR 1
MANR 1

Query Result should be :

MANR 1
MANR 1
ABCD 1
ABCD 1
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL

PostPosted: Mon Apr 25, 2005 5:24 pm
Reply with quote

Gopurs,

Try this one, Its not producing what exactly you want, still doing what you want to do.

SELECT COL1, COL2, COUNT(*) FROM TABNAME GROUP BY COL1, COL2 HAVING (COUNT(*)>1 AND COL2 ='1';

This query produces results... like in your case.....

MANR 1 2
ABCD 1 2

Where 2 at the last column is the count of occurrences.

Regards,

Priyesh
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL

PostPosted: Tue Apr 26, 2005 9:42 am
Reply with quote

Hi Gopur,

Try this query....Here is another version .......It'll be more helpful & as per your requirement.

SELECT COL_1, COL_2 FROM TAB_NAME
WHERE COL_1 IN
( SELECT COL_1 FROM TAB_NAME
GROUP BY COL_1, COL_2 HAVING COUNT(*)>1
)
AND COL_2 = 1;

Regards,

Priyesh.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts To get the count of rows for every 1 ... DB2 3
No new posts To get the the current time DFSORT/ICETOOL 13
No new posts Exclude rows with > than x occurre... DFSORT/ICETOOL 6
No new posts Changeman - how can we know the curr... Compuware & Other Tools 2
No new posts Fetch data from programs execute (dat... DB2 3
Search our Forums:

Back to Top