remove duplicates with specific criteria sql

I am recording the changes made in a directory. That directory has a lot of files so I am trying to reduce the size of my database by deleting duplicates. But to make things clear let me show you what I have.

I have a table named table1

from looking at this table I can tell that the first time I scanned the directory I had two 开发者_开发问答Files A and B. The next time I scanned the directory I know that I have added file C and modified file A by looking at the table. note that file A has a different DateModified that's why I can tel it's been modified. Lastly on my last scan I know that file X has been created and also that file B has been modified. File A and C stayed the same.

After creating a lot of scans there are several files that get repeated. If they have a different DateModified I don't want to remove duplicates because I want to track changes on that directory. Moreover when removing duplicates I want to stay with the lower DateInserted so that I can know when it was modifies.

So in other words I want to build a query where I can remove FileID 6 because FileID 3 contains the same path and same DateModified. I will also like to remove FileID 4 because fileID 2 cointains the same two records. And lastly I will like to remove fileID 5 for the same reason. How could I build this query?

EDIT

I am just adding the query that I have been working with hoping it can help.

with this query I know that those are the files that repeat based on the criteria that I need. I don't know how to go from there and delete the higher dateInserted duplicated based on those results. Hope this helps

If I understand right, you want unique records where a unique record qualifies as a distinct Path & DateModified combination, and when duplicates occur you want the earliest record returned.

This query should hopefully do the trick: SELECT MIN(FileID) AS FileID,Path,datemodified,dateInserted FROM table1 GROUP BY Path,DateModified