Hello, its hard to explain, i have a table like this:
--userpage_visitors--
id bigint
owner nvarchar(20)
visitor nvarchar(20)
created datetime
Then i have some code like this: (@.Visitor is send to the stored proc)
DECLARE @.lastusernvarchar(20)
SELECTTOP 1 @.lastuser= visitorFROM userpage_visitorsWHERE(owner= @.UserName)ORDERBY createdDESC
IF(@.lastuser<> @.Visitor)
BEGIN
INSERTINTO userpage_visitors(owner, visitor, created)VALUES(@.UserName, @.Visitor, @.Created)
-- delete here
END
Now after i have inserted the new visitor into the table, i need to clean the table... so each user should have maximum of 30 visitors, so if the user i inserted above is the 31st user then i need to delete the first user, so i always have 30 fresch visitors,, if they have less then 30 visitors then nothing should happen. The question is, how can i get the 31th post? in mysql you can say that you want post 30, 31, but in mssql you only have the TOP to select limited posts, any ideas?
Patrick
In Sql 2005 you can actually select limited records using Row_Number()But what you need to do is where you have marked -- delete here
IF (SELECT COUNT(ID) FROM Userpage_Visitors ) > 30
BEGIN
DELETE FROM Userpage_Visitors WHERE ID = (SELECT TOP 1 ID FROM Userpage_Visitors ORDER BY Created)
END
That will delete the oldest entry in the table
