Delete the Relations if there are more than a number(4) per Object/Item

Question

I have 2 Entities Item and Image. An Item can have multiple Images.

If an Item has more than 4 Image(s) I want to delete the ones above, ordered by id

Structure:

Item

id | name
1    a
2    b
3    c

Image

id | item_id
1    1
2    1 
3    2

You can use this to identify which should be kept and which should be removed : https://stackoverflow.com/questions/1124603/grouped-limit-in-postgresql-show-the-first-n-rows-for-each-group — sagi, Dec 16 '18 at 12:52

score 1 · Accepted Answer · answered Dec 16 '18 at 12:52

You could use:

DELETE FROM Image
WHERE id IN (SELECT sub.id
             FROM (SELECT im.id,
                    ROW_NUMBER() OVER(PARTITION BY i.id ORDER BY im.id) AS rn
                   FROM Item i
                   JOIN Image im
                     ON i.id = im.item_id) sub
             WHERE sub.rn > 4);

Before you run DELETE check if subquery returns desired ids.

score 1 · Answer 2 · answered Dec 16 '18 at 12:53

I would do:

delete from image
where id < (select i2.id
            from image i2
            where i2.item_id = i.item_id
            order by id desc
            limit 1 offset 3
           );

This deletes any id that is less than the fourth largest id for a given item_id.

Delete the Relations if there are more than a number(4) per Object/Item

2 Answers2