Selecting duplicate records from a table - Complete record and not just the duplicate keys

Database

Re: Selecting duplicate records from a table - Complete record and not just the duplicate keys

Sorry for that.

Result table should be like:

 NO     Name          YOJ       NCD     PY_Amt        RD_ID    PY_DT

 2       JOE             2010     CN      19010.00      435        04/15/2012

  4       JOHN           2011     RT      23415.00      654        04/28/2014

As for 1&2 , 3&4 records are having duplicate when comparing Name,YOJ,NCD but differening either in PY_Amt ,RD_ID. And we need to pick latest record from this.  Hence expecting 2nd,4th rows in result

Teradata Employee

Re: Selecting duplicate records from a table - Complete record and not just the duplicate keys

select *
from YourTable
qualify rank() over( partition by Name, YOJ, NCD
order by PY_DT desc ) = 1

In the above code It's supposed there aren't dupplicate when comparing Name, YOJ, NCD and PY_DT

Enthusiast

Re: Selecting duplicate records from a table - Complete record and not just the duplicate keys

SEL A.* FROM 

(SEL A.*

FROM 

(SEL TBL.*  FROM TABLE TBL QUALIFY (COUNT(*) OVER(PARTITION BY NAME,YOJ,NCD ORDER BY PY_DT DESC) >1)) A

 QUALIFY COUNT(*) OVER(PARTITION BY NAME,YOJ,NCD,PY_AMT,RD_ID ORDER BY PY_DT DESC)=1

UNION

SEL A.*

FROM 

(SEL TBL.*  FROM TABLE TBL QUALIFY  (COUNT(*) OVER(PARTITION BY NAME,YOJ,NCD ORDER BY PY_DT DESC) >1)) A

QUALIFY  COUNT(*) OVER(PARTITION BY NAME,YOJ,NCD,PY_AMT,RD_ID ORDER BY PY_DT DESC) >1

AND 

(NAME,YOJ,NCD) IN

(SEL * FROM  (SEL A.NAME,A.YOJ,A.NCD FROM 

(SEL TBL.*  FROM TABLE TBL QUALIFY (COUNT(*) OVER(PARTITION BY NAME,YOJ,NCD ORDER BY PY_DT DESC) >1)

)A

QUALIFY 

COUNT(*) OVER(PARTITION BY NAME,YOJ,NCD,PY_AMT,RD_ID ORDER BY PY_DT DESC) =1

)A )

) A

QUALIFY 

ROW_NUMBER() OVER(PARTITION BY NAME,YOJ,NCD ORDER BY PY_DT DESC)=1