This question does pretty much what I want to accomplish, but my table is more complicated and does not have a primary key. I also don't quite understand the top answer, what the t1 and t2 mean. If this answer can be applicable to me, would appreciate if someone explain the code.
I have several months' tables that contain info on clients and the policies they hold. Every client has a unique policy ID, but they can have multiple policies, resulting in multiple records under the same policy ID. The duplicate records can be completely different or exactly the same in every field.
For my purposes, I want to keep only one record for each policy ID. Ideally the record kept is the one with the highest Age, but does not need to if it's too complicated. Note there may be more than one record with the age that is the max for that particular Policy ID, then it doesn't matter which one of those we keep.
I do not plan on creating a primary key because there are some cases when I will be keeping two records under the same policy ID, which I will make the modification to the code myself. I also don't want to create another table because I am working with 10+ tables. Someone suggested using first(), but I'm not sure how to incorporate it into a query.
Please let me know if you need any additional information, and thank you for your help in advance!
=========UPDATE #1
Okay, looks like my question was a bit unrealistic, so I will add an autonumber primary key. How will I proceed with that?
Something on these lines:
DELETE Policies.*
FROM Policies
WHERE Policies.ID Not In (
SELECT TOP 1 id
FROM policies p
WHERE p.policyid = policies.policyid
ORDER BY createdate DESC, id )