We help IT Professionals succeed at work.
Get Started

SQL Looping

67 Views
Last Modified: 2016-03-31
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)

Open in new window


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.
Comment
Watch Question
EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE