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 < (select min
(z.logindatetime ) from
(select top 5 logindatetime from userloginlog where username = b.username
order by logindatetime desc) z )



----- Original Message -----
From: "Alvin Smith "
<mssqldba-ezmlmshield-x87283374.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Wednesday, April 27, 2005 11:41 AM
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]
>
>



MS Sql Server LazyDBA home page