SQL Server Performance Forum – Threads Archive

Max x posts per user

Hello! I have a table that looks like this. – UserId
– VisitorUserId
– VisitDate This table holds the last visits to a user. But we only allow the user to access his x last visists so therefore we only want to store the x lasts visits as well. No need to take up space that we never use. What is the best way to delete the oldest posts over x grouped by UserId? One discussion could be about a good query, and another one about how to execute it. Should it be executed in each INSERT (if count(*) > x). Or maybe a daily night job? Maybe a job executed every minute?

---

Use a query. You’re not giving us the PK, so I’m assuming it’s called XID: SELECT t1.* FROM table t1
WHERE t1.XID
IN (SELECT TOP 5 t2.XID FROM table t2
WHERE t2.UserId = t1.UserId ORDER BY VisitDate DESC)

---

]]>