|
View previous topic :: View next topic
|
| Author |
Message |
babu_hi
New User
Joined: 11 Apr 2006
Posts: 93
|
|
|
|
| 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 |
|
 |
rajandhla
Active User
.jpg)
Joined: 18 Oct 2005
Posts: 182
Location: Luton UK
|
|
|
|
| use group by <on key> and having count(*)>1 |
|
| Back to top |
|
|
babu_hi
New User
Joined: 11 Apr 2006
Posts: 93
|
|
|
|
| Can u please explain clerly? |
|
| Back to top |
|
|
Gurmeet
New User
Joined: 22 Feb 2006
Posts: 46
Location: Pune
|
|
|
|
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 |
|
|
rajandhla
Active User
Joined: 18 Oct 2005
Posts: 182
Location: Luton UK
|
|
|
|
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 |
|
|
die7nadal
Active User
Joined: 23 Mar 2005
Posts: 156
|
|
|
|
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 |
|
|
Gurmeet
New User
Joined: 22 Feb 2006
Posts: 46
Location: Pune
|
|
|
|
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 |
|
|
Gurmeet
New User
Joined: 22 Feb 2006
Posts: 46
Location: Pune
|
|
|
|
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 
Gurmeet |
|
| Back to top |
|
|
die7nadal
Active User
Joined: 23 Mar 2005
Posts: 156
|
|
|
|
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 |
|
|
Gurmeet
New User
Joined: 22 Feb 2006
Posts: 46
Location: Pune
|
|
|
|
| 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 |
|
|
|
|
