SQL - remove all except the last N rows by group

user_5338928 0
Viewed 974

I have a messages table with a client_id column (think of it as different chat channels) and I'd like to remove everything except the last N messages by client_id, meaning that I'd like to keep only the last N messages for each channel in the table.

Example data:

id,message,client_id,submitter
1,hello world,1,someUser
2,another message,3,johndoe
3,hello world,2,testUser
4,hello someUser,1,someoneElse

I could do something like the following, but that would delete everything except the last @limit rows.

DELETE FROM messages WHERE id NOT IN (SELECT id FROM messages ORDER BY id DESC LIMIT @limit)

Is there a way to do it in 1 query instead of selecting distinct client_id and then removing for each?

postgresql sql
1 Answers

You could use a subquery here:

DELETE
FROM messages m1
WHERE id NOT IN (SELECT m2.id
                 FROM messages m2
                 WHERE m2.client_id = m1.client_id
                 ORDER BY m2.id DESC
                 LIMIT 5);

The above logic would prune away all client records which are not the most recent five. Should a given client have fewer than 5 records, then no deletions at all would happen.

user_18632290
Related