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 only dulicates in my table?

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

New User


Joined: 11 Apr 2006
Posts: 93

PostPosted: Fri May 19, 2006 3:38 pm    Post subject: How To Retrieve only dulicates in my table?
Reply with quote

I have a DB2 table that contain duplicates, Now I want to retrieve only duplicates, How can i get, anyone please tell me answr for this question?
Back to top
View user's profile Send private message

rajandhla

Active User


Joined: 18 Oct 2005
Posts: 182
Location: Luton UK

PostPosted: Fri May 19, 2006 3:59 pm    Post subject:
Reply with quote

use group by <on key> and having count(*)>1
Back to top
View user's profile Send private message
babu_hi

New User


Joined: 11 Apr 2006
Posts: 93

PostPosted: Fri May 19, 2006 4:16 pm    Post subject:
Reply with quote

Can u please explain clerly?
Back to top
View user's profile Send private message
Gurmeet

New User


Joined: 22 Feb 2006
Posts: 46
Location: Pune

PostPosted: Fri May 19, 2006 5:31 pm    Post subject: Re: How To Retrieve only dulicates in my table?
Reply with quote

I think what rajandhla means is:

user group by on the primary key of the table where count is greater than 1... good idea...

i m trying it out but not able to get the query right...

rajandhla,

Can you please give a sample query...

Regards,
Gurmeet
Back to top
View user's profile Send private message
rajandhla

Active User


Joined: 18 Oct 2005
Posts: 182
Location: Luton UK

PostPosted: Fri May 19, 2006 6:05 pm    Post subject:
Reply with quote

Babu,

Please find the sample query

SELECT BKG_REF, count(*) as Total_rows
FROM OSCUAT.TOSBKNG
GROUP BY BKG_REF
HAVING COUNT(*)>1;

This query first groups all the rows based on the given key value ( i.e here bkg_ref is the key )then it selects the group of rows which has got more than two 1 row(simillar)

Hope this clarifies....

Regards
jai
Back to top
View user's profile Send private message
die7nadal

Active User


Joined: 23 Mar 2005
Posts: 156

PostPosted: Fri May 19, 2006 6:05 pm    Post subject:
Reply with quote

Gurmeet,
If you use GROUP BY on ur PRIMARY key having COUNT > 1, you will not be returning any rows. Since Primary Key cannot have any dups.

Code:

SELECT C1, COUNT(*)
GROUP BY C1
HAVING COUNT(*)>1
WITH UR;


Where C1 can be one or more Columns, on which you are trying to find the dups.
Back to top
View user's profile Send private message
Gurmeet

New User


Joined: 22 Feb 2006
Posts: 46
Location: Pune

PostPosted: Fri May 19, 2006 6:36 pm    Post subject: Re: How To Retrieve only dulicates in my table?
Reply with quote

die7nadal,

Oopss, i got it wrong...

actually there are certain cases on our development environment where in we have duplicates(primary key), though its theoritically incorrect.

Gurmeet
Back to top
View user's profile Send private message
Gurmeet

New User


Joined: 22 Feb 2006
Posts: 46
Location: Pune

PostPosted: Fri May 19, 2006 6:38 pm    Post subject: Re: How To Retrieve only dulicates in my table?
Reply with quote

die7nadal,

Oopss, i got it wrong...

actually there are certain cases on our development environment where in we have duplicates(primary key), though its theoritically incorrect.

Thanks for your help,i got the correct query now, it worked icon_smile.gif
Gurmeet
Back to top
View user's profile Send private message
die7nadal

Active User


Joined: 23 Mar 2005
Posts: 156

PostPosted: Sat May 20, 2006 8:52 pm    Post subject:
Reply with quote

Just curious.

What did u mean by a primary key, a P Key must have a unique index, in which case it cannot have dups. If it has dups, then it shud have duplicate Index defined on it, in that case it is a Duplicate Key.
Back to top
View user's profile Send private message
Gurmeet

New User


Joined: 22 Feb 2006
Posts: 46
Location: Pune

PostPosted: Mon May 22, 2006 5:43 pm    Post subject:
Reply with quote

Yes you are right... but in our development environment some of the DBs are in a terrible state. We have unique primary indexes defined on the table but then also found duplicates, really not sure how the earlier DBAs managed to do this....
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 SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Need to retrieve Julian_date Suganya87 DB2 4 Wed Aug 17, 2016 7:27 pm
No new posts Updating online Db2 table kishpra DB2 3 Fri Aug 12, 2016 2:44 pm


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