Select Only Max 3 Rows From The Same User - MySQL

Question

Let's say there are two tables:

Table "photos" with columns:

id,
title,
path,
user_id

And table "users" with columns:

id
username

What I want to do is select let's say 30 photos from the "photos" table but with condition that there are at most 3 photos from the same user in the fetched result set. So far I have this query:

SELECT p.*, u.username FROM photos AS p 
INNER JOIN users AS u ON u.id = p.user_id 
ORDER BY p.id DESC LIMIT 30;

How to create a WHERE clause to achieve max. 3 rows from the same user in the fetched result set?

EDIT: I'm using MySQL 5.1.33

Answer 1

SELECT p.*, u.username 
FROM photos AS p 
INNER JOIN users AS u ON u.id = p.user_id 
WHERE p.id in (
 SELECT id  
 FROM photos p
 WHERE user_id  = p.user_id 
 LIMIT 3
)
ORDER BY p.id DESC LIMIT 30;

Answer 2

Hand translated from T-SQL, so untested on MySQL.

SELECT p.*, u.username
FROM photos AS p
INNER JOIN users AS u ON u.id = p.user_id
WHERE p.id IN (
 SELECT p1.id
 FROM photos p1
 JOIN photos p2
 ON p1.user_id = IFNULL( p2.user_id, p1.user_id )
 AND p1.id <= IFNULL( p2.id, p1.id )
 GROUP BY p1.id
 HAVING COUNT(*) < 4
)
LIMIT 30

Answer 3

I would look at this technique. Your where clause would be WHERE row_number <3

and then limit 30

Answer 4

Ok, here is a "clever" way to do it:

SELECT u.username, p1.* FROM photos AS p1
 INNER JOIN users AS u ON u.id=p1.user_id
 LEFT OUTER JOIN photos AS p2 ON p2.user_id=p1.user_id and p2.id <= p1.id
 GROUP BY p1.id
 HAVING COUNT(p2.id) <= 3
 LIMIT 30;

Having said that, be weary of "clever" code. It makes you feel good in the near term but maintenance can be painful.