Q:

Removing duplicates based on a "grouping"?

I currently have a one-to-many relationship with users and profile images that I need to change to be a one-to-one relationship.
I have a Users table, and then an ImageReference table that has a UserID.
What I need to do is for each User that has more than one associated ImageReference record, remove all ImageReference records EXCEPT for the most recent one.
So if a User has two image references, one with an ID of 100, and one with an ID of 150, I need to remove the one with the ID of 100.
Any idea on the best way of doing this?

A:

Insert all of the latest ImageReference rows (select max ID grouping by user ID) into a temporary table, then left join that table back on the ImageReference table delete those rows that do not join (where the ImageReference.ID is null). 

