Delete Duplicated Row in SQL Server

1. Truncate NoDup table

2. Create CTE1 to get the max MyTime group by FileName and Month

TRUNCATE TABLE NoDup;

;WITH CTE1 AS
(SELECT FileName, Max(MyTime) AS MaxMyTime
FROM SourceTableDup
GROUP BY FileName, CONVERT(VARCHAR(10),MyTime,10))

3. Select the target columns from SourceTable

4. Join the CTE1 on FileName and MyTime

5. Insert into NoDup table

INSERT INTO NoDup
SELECT Col1, Col2, Col3
FROM SourceTableDup INNER JOIN CTE1 
ON SourceTableDup.FileName = CTE1.TargetFileName
AND SourceTableDup.MyTime = CTE1.MaxMyTime

6. Truncate source table

7. Insert into source table from NoDup table

TRUNCATE TABLE SourceTableDup;
INSERT INTO SourceTableDup
SELECT * FROM NoDup;

Reference:

http://support.microsoft.com/kb/139444

http://www.orafaq.com/faq/how_does_one_eliminate_duplicates_rows_from_a_table