How do you identify duplicates and how do you eliminates duplicates from the table?

Don’t use distinct query for identifying duplicate values.

Correct way is Group By and Having

SELECT PartyID FROM Party

Group By PartyID

Having Count(*)>1

Elimination: (To remove recent ) record

Delete FROM Test WHERE Test .%%Physloc %%

NOT IN(SELECT min (%%PHysloc %%)

FROM Test Group by TID)

Physloc: It is used to eliminate the duplicates. i.e assigns nuber to every new record.

OR

By using CTE:

WITH CT (TID,Ranking)

AS

(SELECT TID,Ranking = Dense+_Rank() Over

(partition by TID order by NewID())

FROM Test

)

Delete FROM CT WHERE Ranking>)