Hi

I have a table that has duplicate records but still I am unable to identify them with a unique field.

Can anybody help me in finding the duplicate records and how to discard them from the table?

Note : I need the Microsoft SQL Query not related to Oracle.

Table Structure is

Sno     Sal
1        1000
2        4000
3        2000
4        3000
1        1000
2        4000
1        1000
2        4000
4        3000
3        2000
2        4000

Approach - 1
WITH DUPLICATEROW (ROW, GROUPROW,SNO,SAL)
AS
(
SELECT ROW,
          GROUPROW = CASE WHEN SNO=B.SNO
                                  THEN
                                         (SELECT COUNT(*) FROM (SELECT ROW_NUMBER() OVER (ORDER BY SNO) AS ROW,
                                                                                         SNO,SAL FROM TEMP1
                                                                                         ) AS A
                                  WHERE
                                                A.SNO=B.SNO AND
                                                A.ROW<=B.ROW)
                                  END,
          SNO,
          SAL
FROM
       (SELECT
                     ROW_NUMBER() OVER (ORDER BY SNO) AS ROW,
                     SNO,
                     SAL
        FROM
                     TEMP1
       )AS B
)
DELETE FROM DUPLICATEROW WHERE GROUPROW<>1

Approach – 2 ( Three step approach)
SELECT DISTINCT * INTO #DUPES FROM TEMP1

DELETE FROM TEMP1 WHERE SNO IN (SELECT SNO FROM TEMP1 GROUP BY SNO HAVING COUNT(*) >=2)

INSERT TEMP1
       SELECT * FROM #DUPES