nashiooka
asked on
SQL Looping
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:
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.
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.
Jim Horn
Eyeballeth thy article on SQL Server Delete Duplicate Rows Solutions, scroll down to the 'Delete #1 section', and change the query to meet your needs and your definition of '10 newest entries', then change WHERE row_number > 1 to > 10.
I can't understand your question. Why do you want an unique user?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry this took so long. I kind of got distracted off the SQL learning path. The selected solution however was exactly what I needed. Thanks it was enlightening.