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
 
To find the duplicate set of rows

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

New User


Joined: 14 Mar 2009
Posts: 6
Location: Hyderabad

PostPosted: Tue Mar 17, 2009 12:45 pm    Post subject: To find the duplicate set of rows
Reply with quote

WE HAVE A TABLE WITH X COLUMNS AND THE QUERY IS TO FIND THE
NUMBER OF ROWS WHICH HAVE DUPLICATE VALUES IN
THREE COLUMNS.

FOR EXAMPLE IN TABLE TAB1

WE HAVE FOUR COLUMNS
Code:
COL1      COL2   COL3  COL4
01        342    232   2112
02        342    232   2342
03        342    232   2342
04        342    441   2342

If we Want the number of rows having duplicate values in set of COL2, COL3 AND COL4, the result of the rows query should be like this:

Code:
COL1 , COL2, COL3, COL4
02     342    232  2342
03     342    232  2342


Edited: Please use BBcode when You post some code/error, that's rather readable, Thanks... Anuj

Warning: Turn Off Caps
Back to top
View user's profile Send private message

Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Tue Mar 17, 2009 3:17 pm    Post subject: Reply to: TO FIND THE DUPLICATE SET OF ROWS
Reply with quote

Rupali,
Try the following Query and let us know if suits your requirement.
Code:
SELECT DISTINCT X.A,X.B,X.C,X.D                             
FROM EMP X ,(SELECT A,B,C,D FROM EMP ) AS Y
WHERE X.B = Y.B                                             
AND   X.C = Y.C                                             
AND   X.D = Y.D                                             
AND   X.A <> Y.A ;

Replace EMP with your table name and A,B,C,D with your respective column names.
WTF
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Tue Mar 17, 2009 3:17 pm    Post subject:
Reply with quote

Hi,

If I understood correctly, try something like:
Code:
select col2,col3, col4, count(*)
from table
group by col2,col3, col4
having count(*) > 1
Back to top
View user's profile Send private message
senthilnathanj

New User


Joined: 31 Jul 2007
Posts: 47
Location: chennai

PostPosted: Tue Mar 17, 2009 5:10 pm    Post subject:
Reply with quote

Hi rupali,
Try the following query and let us know...
Code:
WITH TEMP(COL1,COL2) AS (
SELECT COL1,COL2 FROM TAB1 GROUP BY COL1,COL2 HAVING COUNT(*) > 2)
SELECT A.COL1,A.COL2 FROM TAB1 A WHERE A.COL1 IN (SELECT T.COL1 FROM TEMP T) AND A.COL2 IN (SELECT T.COL2 FROM TEMP T);


u can get the extract output..
Back to top
View user's profile Send private message
gkanswa

New User


Joined: 09 Feb 2009
Posts: 5
Location: Pune

PostPosted: Thu Mar 19, 2009 5:43 pm    Post subject:
Reply with quote

Hi Rupali,

You can try this one -

Code:
Select * from TAB1 A
where 2 <= (select count (distinct Col2) from TAB1
                   where COL2= A.COL2  and
                             COL3 = A.COL3 and
                             COL4= A.COL4);
"Code"d

I don't have mainframe access, so couldn't test it.
Please do correct me if I am wrong.

Girish
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 Merging 2 records at multiple rows wi... Bijesh DFSORT/ICETOOL 1 Wed Dec 06, 2017 1:50 am
No new posts Can we combine the rows with same key... V S Amarendra Reddy DB2 12 Fri Dec 01, 2017 10:29 pm
No new posts Conditional highlighting of rows in I... packerm CLIST & REXX 2 Thu Sep 21, 2017 6:50 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm

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