AOB
Registered User.
- Local time
- Today, 06:29
- Joined
- Sep 26, 2012
- Messages
- 613
Hi guys,
I have a table of records, multiple fields but key fields here are a UserID (Text) and a Login timestamp (Date/Time). A UserID may appear across multiple records (different devices).
I want to run a delete query that deletes all but the most recent record for each distinct UserID, based on the Login timestamp.
i.e. If a particular UserID appears 5 times in the table, delete the 4 oldest records according to the Login timestamp and retain just the most recent record. If a UserID appears just once, simply retain that record.
I've been messing with ranks and self joins and either my subquery is returning the wrong subset of data or the engine simply won't allow the operation at all.
I'm pretty sure this should be straightforward enough but I can't figure it out.
:banghead:
Any suggestions?
Thanks
AOB
I have a table of records, multiple fields but key fields here are a UserID (Text) and a Login timestamp (Date/Time). A UserID may appear across multiple records (different devices).
I want to run a delete query that deletes all but the most recent record for each distinct UserID, based on the Login timestamp.
i.e. If a particular UserID appears 5 times in the table, delete the 4 oldest records according to the Login timestamp and retain just the most recent record. If a UserID appears just once, simply retain that record.
I've been messing with ranks and self joins and either my subquery is returning the wrong subset of data or the engine simply won't allow the operation at all.
I'm pretty sure this should be straightforward enough but I can't figure it out.
:banghead:
Any suggestions?
Thanks
AOB