title: Mysql get top 5 results per group on inner join
body: <p>I have built websites with complex queries (for me anyway) in the past but I decided that due to the way I program, I should come up with the db queries I need before I build out my site. This way I can avoid re-structuring etc. but I don't have the actual data to play with. So, on my site a user will post. This post can then be re-posted (which I call a "TRICKLE") by any other user. I need to get the top 5 posts from each tag based on how many times it was re-posted.
  So if "baseball" was the tag and 3000 users had a post with that tag, I need to get the top 5 that were re-posted.
  Structure:</p>
  
  <pre><code>POSTS
  -post_id
  -member_id
  -tag
  -title
  -post
  -date_posted
  -is_active
  
  TRICKLES
  -post_id
  -member_id
  -date_trickled
  </code></pre>
  
  <p>I have this query I think is along the lines..</p>
  
  <pre><code>select p.post_id, p.tag, p.title, count(p.post_id) from (
  select * from posts p inner join
  trickles t on t.post_id=p.post_id
  group by p.tag order by count(p.post_id) desc limit 5
  ) order by p.tag asc
  </code></pre>
  
  <p>To me this is saying.
  First (inner query) get all posts and their associated trickle(re-post)
  and group them by their tag and then order by the top count per post and limit it to 5.
  Then get the id, tag, title, count(the total times that post was trickled)
  and order it alphabetically by tag</p>
  
  <p>I'm thinking this will give me the top 5 trickles per group in the database but its tough to figure this out, for me, without the actual data, and I won't until people join and start posting. Every time I start thinking in depth about it I feel like my mind gets jumbled.
  From what I have said, will this query get me the top 5 re-post for every common "tag" in the db? I appreciate this in advance!</p>