假设您想要的最终结果是一个包含这 3 行的表格

Item Ref1 Ref2 时间戳1 测试1 2/3/2012 12:00:002 B prod2 2/3/2012 11:00:002 A prod2 2/3/2012 12:00:00

类似的东西

DELETE FROM table_name a存在的地方(选择 1FROM table_name b哪里 a.item = b.itemAND a.ref1 = b.ref1和 a.timestamp <b.时间戳);

应该假设没有两行具有相同的 Item 和 Ref1 并且具有相同的 Timestamp.如果可以有多个具有相同 Item 和 Ref1 的行，并且它们都具有最新的 Timestamp 并且假设您不关心是哪一个保持

DELETE FROM table_name a存在的地方(选择 1FROM table_name b哪里 a.item = b.itemAND a.ref1 = b.ref1AND a.timestamp 

we have a scenario here where we need to delete all the duplicate rows from a table based on timestamp. The table structure looks like this:

Item   Ref1   Ref2        Timestamp
1      A       test1      2/3/2012 10:00:00
1      A       test2      2/3/2012 11:00:00
1      A       test1      2/3/2012 12:00:00
2      A       prod1      2/3/2012 10:00:00
2      B       prod2      2/3/2012 11:00:00
2      A       prod2      2/3/2012 12:00:00

So we need to delete the duplicate rows from this table based on item and ref1. like here we should have only 1 row for item 1 and ref1 A with the latest timestamp. Same for item 2 we should have only 1 row for ref1 A with latest timestamp.

Any pointers will be great

Assuming that your desired end result is a table with these 3 rows

Item   Ref1   Ref2        Timestamp
1      A       test1      2/3/2012 12:00:00
2      B       prod2      2/3/2012 11:00:00
2      A       prod2      2/3/2012 12:00:00

Something like

DELETE FROM table_name a
 WHERE EXISTS( SELECT 1
                 FROM table_name b
                WHERE a.item = b.item
                  AND a.ref1 = b.ref1
                  AND a.timestamp < b.timestamp );

should work assuming that there are no two rows with the same Item and Ref1 that both have the same Timestamp. If there can be multiple rows with the same Item and Ref1 that both have the latest Timestamp and assuming that you don't care which one you keep

DELETE FROM table_name a
 WHERE EXISTS( SELECT 1
                 FROM table_name b
                WHERE a.item = b.item
                  AND a.ref1 = b.ref1
                  AND a.timestamp <= b.timestamp 
                  AND a.rowid     <  b.rowid);