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
 

 

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 To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 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 Sort Card to Remove Duplicate records... raj4neo SYNCSORT 2 Wed Jan 25, 2017 4:44 am
This topic is locked: you cannot edit posts or make replies. DB2 query Using Recursion, Converting... smilewithashu2 DB2 3 Tue Jan 03, 2017 12:50 pm
No new posts find particular member name in PDS us... ravi243 CLIST & REXX 10 Mon Dec 19, 2016 6:44 pm


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