No. It will delete all of them that fit the criteria. You can search
for duplicates, put those dupes (only one record of each) into another
table using a SELECT INTO, then run the query to delete them in the
original table, and then import the individual records from the second
table back into the first. That way you'll have only one copy of each.
However if you just want to keep the most recent record, customize your
WHERE clause to find the most recent record and to delete all the other
ones that are older than that one.

Laurie

-----Original Message-----
From: Alvin Smith
[mailto:mssqldba-ezmlmshield-x87283374.[Email address protected]
Sent: Wednesday, April 27, 2005 9:42 AM
To: LazyDBA Discussion
Subject: Prune Old Records

I have the following bit of sql:

DELETE FROM a
FROM UserLoginLog a JOIN
(SELECT UserName, max(LoginDateTime) Max_Date FROM UserLoginLog
GROUP BY UserName
HAVING COUNT(*) > 1) b
ON a.UserName = b.UserName and a.LoginDateTime < b.Max_Date

And it deletes all records with multiple usernames except the newest
one.
Does anyone know how I can alter this code to make it such that it
deletes
all records with multiple usernames except the newest FIVE entries? X
entries?

Thanks,

Alvin


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]



This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you.
MS Sql Server LazyDBA home page