DELETE all rows exept the last five
-
Hello!
I thought this would be simple but when I started to write the procedure I didn't know where to begin exactly. This is my table:
UserLogins
logID
usrID
IP
DateLogged
What I'm trying to do is move all the rows to a table called logUserLogins (which has the same structure as the original table). But I want to leave max five rows for each usrId.
My idea was to move all the rows to the new table and then copy each user's five newest logs back to the original table. I thought it would be simple but no, no.
/Tomi
PS. Why can't you edit the subject? It should be move not delete.
-
I am assuming that when you said "last five" you are using the date field for that and also that LogId is PK, so:
DELETE FROM TBL
JOIN
(
Select a.LogID
From
(SELECT LogID, (SELECT COUNT(*)
FROM TBL
WHERE UsrID = s.UsrID
AND DateLogged <= s.DateLogged) Rank
FROM TBL s) a
where a.Rank >=5
) Del_Set ON TBL.LogID = Del_Set.LogID
HTH
* Noel -
DELETE FROM TBL
where LogID not in
(
select top 5 LogID
from TBL
order by DateLogged desc
)
-
Thanks!
I'll check it out...
-
alkanfer. I do not know if I misread the question or not, but your code is deleting everything apart from the FIRST five rows, but tomiz appears to want to delete the LAST five rows.
------------------------------
The Users are always right - when I'm not wrong! -
Yes, that is true. Luckily i managed to figure the code out.
Thanks for all your help!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply