CODEWITHSUNDEEP
mysqlgroup-byinner-joingreatest-n-per-group
Naterade
Naterade

Reputation: 2675

Mysql get top 5 results per group on inner join

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:

POSTS
-post_id
-member_id
-tag
-title
-post
-date_posted
-is_active

TRICKLES
-post_id
-member_id
-date_trickled

I have this query I think is along the lines..

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

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

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!

Upvotes: 2

Views: 1823

Answers (1)

denied
denied

Reputation: 608

I suppose this trick with rows enumeration will help you. It is a little bit complicated, but should work properly :

select 
    j.tag,
    j.post_id,
    j.title,
    j.cnt
from (
            select
                case when @b <> i.tag then (select @a := 0) end tmp1,
                case when @b <> i.tag then (select @b := i.tag) end tmp2,
                (select @a := @a + 1) num,
                i.*
            from (
                        select
                            p.title,
                            p.post_id, 
                            p.tag, 
                            count(*) cnt
                        from posts p
                        left join tickles t on t.post_id = p.post_id
                        group by 
                            p.post_id, p.tag, p.title
                        order by p.tag, count(*) desc
            ) i
            left join (select @a := 0, @b := '') x on 1=1 
) j
where j.num <= 5

Upvotes: 2

Related Questions