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 to retrieve mutliple current rows

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

New User


Joined: 04 Apr 2005
Posts: 9

PostPosted: Fri Apr 22, 2005 1:10 pm    Post subject: How to retrieve mutliple current rows
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: 1452
Location: Chicago, IL

PostPosted: Mon Apr 25, 2005 5:24 pm    Post subject: Re: How to retrieve mutliple current rows
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: 1452
Location: Chicago, IL

PostPosted: Tue Apr 26, 2005 9:42 am    Post subject: Re: How to retrieve mutliple current rows
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    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 To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts How to get current date -1 and curren... murali.andaluri DFSORT/ICETOOL 3 Wed Mar 08, 2017 2:09 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
No new posts To Merge mutliple records into a sing... anandgbe DFSORT/ICETOOL 6 Wed Feb 22, 2017 8:49 am
No new posts Convert current to hex value gopurs DFSORT/ICETOOL 4 Wed Feb 15, 2017 5:40 am


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