Keep the newest and delete the oldest

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Keep the newest and delete the oldest

Author

Topic

CMartin
Starting Member

13 Posts

Posted - 2002-08-16 : 19:43:26

The following table [LogInfo] registers, per MemberID, logged info about visitor ip and browser type from people that accesses their (members) web sites.

RecID int (PK identity)
MemberID int
IPID int
BrowserID int
HitDate datetime

I want to keep, per MemberID, only the most 1000 recent records and the oldest (more than 1000) delete them.

What is the best way to delete, per MemberID, only the old records (that exceed 1000)?
This task could be made in a daily basis or each time a new record is inserted.

Carlos

zippy
Yak Posting Veteran

69 Posts

Posted - 2002-08-16 : 21:56:17

Just off the top of my head...
something like:
DELETE
FROM LogInfo
WHERE RecID NOT IN
(
SELECT TOP 1000 RecID
FROM LogInfo
ORDER BY HidDate DESC
)

Should do the trick, might be a more efficent way to do it though..

Check out the worlds fastest computers at http://www.ocgurus.com

Edited by - zippy on 08/16/2002 21:56:56

sandesh_moghe
Constraint Violating Yak Guru

310 Posts

Posted - 2002-08-16 : 21:57:42

Why dont you create a view having such conditions and keep the table in back for history purpose.

---------------------------
Sandesh - The Messanger

CMartin
Starting Member

13 Posts

Posted - 2002-08-17 : 12:51:19

zippy,

That will work with an aditional line with MemberID to control recs by Member, every time a new record is inserted.

What about as a daily task, where I need to delete records BY MemberID?

Carlos

Edited by - CMartin on 08/17/2002 13:37:26

CMartin
Starting Member

13 Posts

Posted - 2002-08-17 : 12:56:27

Sandesh,

quote:

Why dont you create a view having such conditions and keep the table in back for history purpose.

I don't want to keep an history file due to database storage:
If 5000 members get about 1000 clicks a day, that's about 3,5 GB/month

Carlos

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources