I'm quite new to T-SQL, so bear with me.
I have a table with columns UserName, PCName, Date. There is no key field and there may be many entries from the same user name. I only want to keep the 10 newest entries for each unique user name. I've written code like below:
DELETE FROM dbo.PCs WHERE UserName = 'MyUserName' AND
Date NOT IN (SELECT TOP 10 Date FROM dbo.PCs WHERE UserName = 'MyUserName' ORDER BY Date DESC)
This works fine but how do I expand it and run it for each unique user. I can isolate the unique users easily enough with: SELECT DISTINCT UserName FROM dbo.PCs , but how do I loop through the result.
I do understand that looping isn't a favored approach in SQL, but I'm also having trouble figuring our how to do this any other way. I'm not quite grasping JOINs and such. I'm certainly open to alternatives to looping, but it may take some explaining.