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

228

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)";

160

Answer

Solution:

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

Source

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