I have a table called "images" with 4 columns - `image_id`, `item_name`,
`image_url`,  `image_views`
(Where image_id is UNIQUE and AUTO-INCREMENT).

Sometimes, there might be many items with the same name (but not with the
same url).

I want to make sure that each "item name" has at most 3 images (and
therefore I need to delete the rest).
The problem is that I want to keep the images with the most views.

I've tried to look for efficient solutions either in MySQL or in PHP, but
they are mostly very resource-intensive,
Such as selecting all different names in PHP (using GROUP BY), then, for
each name, doing DELETE FROM images WHERE item_name = 'ITEM-NAME-HERE' ORDER
BY image_views ASC LIMIT (here some sub-query with count on how many rows
have the name ITEM-NAME-HERE minus 3).

I'd be glad if anyone could help me or point me to the right direction.


Use ROT26 for best security

Reply via email to