MYSQL select statement - 5 results from each user

Question

I'm trying to do a select statement and it works except that it's not limiting the number of results for each user (U.id) to 5.

SELECT F.id,F.created,U.username,U.fullname,U.id,I.id,I.cached_image 
FROM favorites AS F 
INNER JOIN users AS U 
ON F.faver_profile_id = U.id 
INNER JOIN items AS I 
ON F.notice_id = I.id 
WHERE faver_profile_id IN ('.$users.')
GROUP BY I.id HAVING COUNT(U.id) <= 5 
ORDER BY F.faver_profile_id, F.created DESC

I'm grouping by I.id to eliminate duplicates. From my research it looks like you can only use HAVING COUNT if your also grouping by that column, but I cannot group by U.id or I'd lose results rows.

Answer 1

Instead of HAVING, can you slap a LIMIT 5 in there?

Edit: OP cannot LIMIT entire query,
and, AFAIK, MySQL does not support LIMIT in subqueries,
so you can create a temporary table with your five (5) user ids:

create table temp_table ( id INT );
insert into temp_table (id) SELECT U.id FROM users U LIMIT 5;

SELECT F.id,F.created,U.username,U.fullname,U.id,I.id,I.cached_image 
FROM   favorites AS F 
       INNER JOIN temp_table AS Ut 
       ON F.faver_profile_id = Ut.id 
       INNER JOIN items AS I 
       ON F.notice_id = I.id 
WHERE faver_profile_id IN ('.$users.')
GROUP BY I.id 
ORDER BY F.faver_profile_id, F.created DESC;

drop table temp_Table;

Let us know how that works.