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 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 CICS RETRIEVE pshongal CICS 2 Thu Mar 08, 2018 9:15 am
No new posts help to replace columns by rows Vikas Maharnawar DFSORT/ICETOOL 8 Tue Mar 06, 2018 3:27 pm
No new posts Merging 2 records at multiple rows wi... Bijesh DFSORT/ICETOOL 2 Wed Dec 06, 2017 1:50 am
No new posts Can we combine the rows with same key... V S Amarendra Reddy DB2 14 Fri Dec 01, 2017 10:29 pm
No new posts Fetching data from JHS as per the cur... arunsoods All Other Mainframe Topics 4 Wed Nov 22, 2017 1:54 pm

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