Forum Moderators: open
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?