Forum Moderators: open

Message Too Old, No Replies

Delete Duplicate Records

With a twist...

         

mattglet

5:00 pm on Feb 21, 2007 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



This is another "how do I delete duplicate records" question, with a little snag involved.

I have a 3 column table which tracks user login times (the ID field is the primary key):

ID¦UserID¦TimeStamp
1¦100¦1/1/2007 12:00:00 AM
2¦100¦1/1/2007 12:01:00 AM
3¦100¦1/1/2007 12:02:00 AM
4¦501¦1/1/2007 1:00:00 AM
5¦501¦1/1/2007 2:15:00 AM
6¦100¦1/1/2007 6:00:00 AM
7¦100¦1/1/2007 6:01:00 AM

I had a programming hiccup where the logging function was incorrectly called throughout the user's session, so I ended up with a ton of duplicate records (the layout above is just for simplicity's sake).

I need to remove all duplicates within a 1 hour timeframe, so I need to end up with the following results in my table:

ID¦UserID¦TimeStamp
1¦100¦1/1/2007 12:00:00 AM
4¦501¦1/1/2007 1:00:00 AM
5¦501¦1/1/2007 2:15:00 AM
6¦100¦1/1/2007 6:00:00 AM

I found a ton of examples on how to delete duplicates, but in those examples I would have deleted row 6 as well (which I need to keep because it's outside the range requirement). I can't figure out how to filter out where the time difference is within an hour only. Can anyone shed some light?