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 retreive only duplicates

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

New User


Joined: 26 Dec 2007
Posts: 8
Location: Chennai

PostPosted: Thu Jan 31, 2008 11:03 am    Post subject: To retreive only duplicates
Reply with quote

Hi,
I have a table which contains 3 columns which can allow duplicates. Now i want to write a query so that i want to retreive ONLY DUPLICATES for all the columns.

Thnaks in Advance,
Vijay
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jan 31, 2008 9:13 pm    Post subject:
Reply with quote

Hello,

How many rows are in the table?

What if there are more than 2 rows that have all 3 columns duplicated? Do you want to retrieve ALL of the duplicates or only those rows that are not the "first" row for a particular set of values (i.e. if 2 rows match, do you want both selected or only 1 - if 3 rows match, do you want all 3 or only 2)?
Back to top
View user's profile Send private message
bbessa

New User


Joined: 03 Aug 2006
Posts: 13
Location: Brazil

PostPosted: Fri Feb 01, 2008 2:58 am    Post subject:
Reply with quote

Hi.

Assuming you just want to show distinct rows that are duplicated and a row count for how many duplicates exist:

Code:

SELECT COL1, COL2, COL3, ROWCOUNT FROM
(
  SELECT DISTINCT COL1,COL2,COL3,COUNT(*) AS ROWCOUNT
  FROM OWNER.TABLE
  GROUP BY COL1,COL2,COL3
 ) AS T
WHERE ROWCOUNT > 1


I don't really know if it's the best solution but it does the job.

If the results provided by this query are not what you wanted then you might want to look into mr. scherrer's questions in the previous post and supply more details on your demand.

My regards,

Bernardo
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 Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Updating the counters after eliminati... PANDU1 DFSORT/ICETOOL 12 Mon Nov 21, 2016 9:47 am
No new posts SORT CARD to remove duplicates & ... mkk157 DFSORT/ICETOOL 17 Tue Jul 08, 2014 9:04 pm
No new posts Remove the duplicates without sorting... maki_psg JCL & VSAM 13 Tue May 13, 2014 10:32 pm
No new posts Having issues removing duplicates thesumitk JCL & VSAM 8 Sat Apr 26, 2014 4:01 pm


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