php - Remove all entries except last 15 from database for each UserID


I have a SQL server database and I would like to delete every row except the last 15, but, I need this to be per 'UserID'.

My database contains entries for a number of different users, where their ID is column 'UserID', so I want each user to have 15 entries in the database and the old ones to be removed.

What would the SQL be for this?

Here is an example effort from myself:

$sql = "DELETE FROM SocialSenseTracking WHERE UserID NOT IN (SELECT TOP 15 UserID='$user' FROM SocialSenseTracking ORDER BY UserID DESC)";



You can use a CTE androw_number() for this:

with todelete as (
      select sst.*, row_number() over (partition by UserId order by CreatedAt desc) as seqnum
      from SocialSenseTracking sst
delete from todelete
    where seqnum > 15;

You don't specify the column used to determine the most recent records, so I just inventedCreatedAt.

People are also looking for solutions to the problem: php - Woocommerce Payment Gateway Based Checkout Fields


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.