title: Limit results on an GROUP_CONCAT() or INNER JOIN
body: <p>I've perused extensively the other threads talking about limits on group_concat() and inner joins but haven't found my answer, so I guess I'll go ahead and ask it:</p>
  
  <p>I'm developing an existing photo community site. I want to retrieve members who have their birthday on a given day (today) and then retrieve each member's 5 most highly rated photos. But I also only want the 10 "most favorite" birthday members (ie with the highest favorite count). Here's what I have:</p>
  
  <pre><code>SELECT users.user_id, users.user_name, 
         GROUP_CONCAT(CONVERT(photos.photo_id,char(32)) 
                      ORDER BY photos.average_rate) as photo_ids
  FROM users 
  INNER JOIN photos ON photos.user_id=users.user_id
  WHERE users.day_of_birth = DATE_FORMAT('2012-04-17', '%m-%d') 
    AND users.photo_count&gt;0 
  GROUP BY users.user_id
  ORDER BY users.favorite_count DESC, users.photo_count DESC LIMIT 0,10
  </code></pre>
  
  <p>This does what I want, EXCEPT that I cannot limit the amount of <code>photo_id</code>s to 5. This is a problem since the output will be sent as JSON to the app, and some members have uploaded upwards of 20,000 photos already, leading to an unacceptably long output string. The only "solution" that seems to work for me is setting the sever variable <code>group_concat_max_len</code> to something reasonable that will hold at least 5 ids, but this is very hacky and unreliable. Is there any way to return exactly 5 <code>photo_id</code>s per user with a single query? Or will I need to do a loop in my PHP?</p>
  
  <p>I don't necessarily need the photo_ids in a comma-separated value, I can also ditch the group_concat() approach entirely and do an inner join if that is more feasible. But even there I'm not aware of a way to limit the results to 5.</p>