How to select maximum 3 items per users in MySQL?

database, mysql, sql

Solution

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! :-)

Problem

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

Original source