How to select maximum 3 items per users in MySQL?

Question

I run a website where users can post items (e.g. pictures). The items are stored in a MySQL database.

I want to query for the last ten posted items BUT with the constraint of a maximum of 3 items can come from any single user.

What is the best way of doing it? My preferred solution is a constraint that is put on the SQL query requesting the last ten items. But ideas on how to set up the database design is very welcome.

Thanks in advance!

BR

Answer 1

It's pretty easy with a correlated sub-query:

SELECT `img`.`id` , `img`.`userid`
FROM `img`
WHERE 3 > (
SELECT count( * )
FROM `img` AS `img1`
WHERE `img`.`userid` = `img1`.`userid`
AND `img`.`id` > `img1`.`id` )
ORDER BY `img`.`id` DESC
LIMIT 10 

The query assumes that larger id means added later

Correlated sub-queries are a powerful tool! :-)