Removing single occurance of rows from the set of duplicate rows

Database

Removing single occurance of rows from the set of duplicate rows

Hi All,

I have a table as follows

Table Name ==> MY_PKG

PKG_ID    P_ID     P_CD

123         ABCD     809

103         ABCD     812

123         BCAD     809

103         BCAD     812

231         KKKK     809

231         KKKK     812

231         KKKK     809

231         KKKK     812

111         XYZC     809

111         XYZC     812

121         XYZC     812

121         XYZC     809

Now, in my above table there are multiple occurance of same P_CD with same PKG_ID, but 1. different P_ID or 2. same P_ID.

Suppose, the above table has > 1 lacs records with above combination of data.

And, I would like to retrieve only those set of data i.e. a combination of PKG_ID & P_CD where there is multiple value of P_ID for a same PKG_ID and P_CD.

In order to get that, I used below query,

SELECT  P1.PKG_ID, P1.P_ID, P1.P_CD
FROM MY_PKG P1
INNER JOIN
(
SELECT PKG_ID, P_ID, P_CD
FROM MY_PKG
WHERE P_ID IN (
SELECT P_ID FROM MY_PKG
GROUP BY 1
HAVING COUNT(DISTINCT P_CD) > 1
) GROUP BY 1, 2, 3
) P2
ON P1.PKG_ID = P2.PKG_ID
AND P1. P_ID <> P2.P_ID
AND P1.P_CD = P2.P_CD

ORDER BY 3,1

Now the issue I faced is, I got the data in following manner

PKG_ID    P_ID     P_CD

123         ABCD     809

123         BCAD     809

231         KKKK     809

231         KKKK     809

111         XYZC     809

121         XYZC     809

103         ABCD     812

103         BCAD     812

231         KKKK     812

231         KKKK     812

111         XYZC     812

121         XYZC     812

But, This is NOT my expected output. I actually expected to get the data as below

PKG_ID    P_ID     P_CD

123         ABCD     809

123         BCAD     809

103         ABCD     812

103         BCAD     812

Question :

How could I select only those rows where as for a same PKG_ID and same P_CD, the P_ID is different ?

My query returns that result along with others as well which I don't want. Please suggest.

Thanks,

Tera_gh

3 REPLIES
Enthusiast

Re: Removing single occurance of rows from the set of duplicate rows

mm

Teradata Employee

Re: Removing single occurance of rows from the set of duplicate rows

select PKG_ID, P_ID, P_CD

from MY_PKG

where (PKG_ID, P_CD)

in (SELECT PKG_ID, P_CD FROM MY_PKG

WHERE COUNT(DISTINCT P_ID) > 1

GROUP BY 1, 2)

ORDER BY 3, 1;

Re: Removing single occurance of rows from the set of duplicate rows

Hi All,

Finally, I have found the solution as below

SELECT P1.PKG_ID, P1.P_ID, P1.P_CD
FROM MY_PKG P1
INNER JOIN
(
SELECT PKG_ID, P_ID, P_CD
FROM MY_PKG
WHERE P_ID IN (
SELECT P_ID FROM MY_PKG
GROUP BY 1
HAVING COUNT(DISTINCT P_CD) > 1
) GROUP BY 1, 2, 3
) P2
ON P1.PKG_ID = P2.PKG_ID
AND P1. P_ID <> P2.P_ID
AND P1.P_CD = P2.P_CD

QUALIFY COUNT (*) OVER (PARTITION BY P1.PKG_ID, P1.P_CD) > 1

Thanks All once again.

Tera_gh