One day i got the scripts from DBA but at last i found that scripts added duplicate rows in the table and i got stuck to remove those duplicate rows from that table and finally i found a proper solution from the following link it helps and solved my problem. i am sharing this to every body it may help others as well.
found a solution from the following link :
if you’ve any question regarding the following code i will try to answer every question
DELETE from a cte:
WITH cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY uniqueid ORDER BY col2)'RowRank' FROM Table) DELETE FROM cte WHERE RowRank > 1
ROW_NUMBER() function assigns a number to each row.
PARTITION BY is used to start the numbering over for each item in that group, in this case each value of
uniqueid will start numbering at 1 and go up from there.
ORDER BY determines which order the numbers go in. Since each
uniqueid gets numbered starting at 1, any record with a
ROW_NUMBER() greater than 1 has a duplicate
To get an understanding of how the
ROW_NUMBER() function works, just try it out:
SELECT *,ROW_NUMBER() OVER(PARTITION BY uniqueid ORDER BY col2)'RowRank' FROM Table ORDER BY uniqueid
You can adjust the logic of the
ROW_NUMBER() function to adjust which record you’ll keep or remove.
For instance, perhaps you’d like to do this in multiple steps, first deleting records with the same last name but different first names, you could add last name to the
WITH cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY uniqueid, col3 ORDER BY col2)'RowRank' FROM Table) DELETE FROM cte WHERE RowRank > 1