No your code is not wrong. I didn't look at it closely enough. I was
just replying to what you wrote and not your query code.

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

Are you stating that you think that my initial statement about the
function
of the code below is wrong or that it is not possible to alter MY code
to do
what i want it to do?

-----Original Message-----
From: Laurie Solgon
[mailto:mssqldba-ezmlmshield-x73977536.[Email address protected]
Sent: Wednesday, April 27, 2005 9:48 AM
To: LazyDBA Discussion
Subject: RE: Prune Old Records

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.

---------------------------------------------------------------------
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]


---------------------------------------------------------------------
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