Duplicates Query 1

[pullingteeth]

Hello, I have a table as follows:

User
Item
Event
Timestamp


Due to a coding error, the table has several hundred duplicate rows, as follows:

User / Item / Event / Timestamp

Joe / 1 / A / 5/26/4 12:34
Peter / 1 / A / 5/26/4 12:45

Janice / 4 / B / 5/27/4 8:19
Sherry / 4 / B / 5/27/4 8:26

(The item and event details are duplicated; the user and timestamp details are different.)

I'd like to write a query to find all of the second rows of each pair (second defined as "has later timestamp") in order to delete them. How would you approach this?

Thanks

 

[PHV]

Something like this ?
SELECT B.* FROM yourTable A INNER JOIN yourTable B
ON (A.Item=B.Item) AND (A.Event=B.Event)
WHERE B.Timestamp>A.Timestamp;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244