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