Sql query to pull top 3 records with max votes in each category

Need some help with putting this query together. I'm using Mysql

I have two tables

Video - contains videos uploaded by users

• video_id
• user_id
• category_id

开发者_运维知识库

Vote - contains a vote given by any user for a particular video

• vote_id
• video_id
• user_id

I don't want to hardcode the categories in the query - the Categories are stored in the Category table which has category_id and category_name

I basically want a query that pulls the top 3 videos (ones with max votes) for each category.

Sample Data - Video Table

video_id |  user_id  | category_id
   1          100          10
   2          101          10
   3          102          11
   4          103          11
   5          104          11
   6          105          11
   7          105          12

Sample Data - Vote Table

vote_id  |  video_id |  user_id
  11           3          105
  12           3          102
  13           3          111
  14           3          121
  15           4          200
  16           4          201
  17           1          222

Sample Data - Category Table

category_id  |  category_name
   10               HipHop
   11               Rap
   12               Country

---

This is the type of problem that is trivial to solve with ranking functions. However, since MySQL does not yet support them, it makes it more difficult. In this design, I assumed that video_id was the primary key of the Video table.

Select video_id, user_id, category_id, vote_count, vote_rank
From    (
        Select VoteCounts.video_id, VoteCounts.user_id
            , VoteCounts.category_id, VoteCounts.vote_count
            , (
                Select Count(*) + 1
                From    (
                        Select V1.video_id, V1.user_id, V1.category_id
                            , Count(vote_id) As vote_count
                        From Videos As V1
                            Left Join Votes As V2
                                On V2.video_id = V1.video_id
                        Group By V1.video_id, V1.user_id, V1.category_id
                        )  As VoteCounts1
                Where VoteCounts1.category_id = VoteCounts.category_id
                    And (
                        VoteCounts1.vote_count > VoteCounts.vote_count
                        Or (VoteCounts1.vote_count = VoteCounts.vote_count
                            And VoteCounts1.video_id < VoteCounts.video_id )
                        )
                ) As vote_rank
        From    (
                Select V1.video_id, V1.user_id, V1.category_id
                    , Count(vote_id) As vote_count
                From Videos As V1
                    Left Join Votes As V2
                        On V2.video_id = V1.video_id
                Group By V1.video_id, V1.user_id, V1.category_id
                )  As VoteCounts
        ) As VoteRanks
Where VoteRanks.vote_rank <= 3

---

I don't know about MySQL, but here's how I would try to do it in SQL Server, and bringing perhaps some adjustments depending on the results which make sense.

select Category
        , Film
        , MAX(Votes) as Votes
    from (
        select TOP 3 c.category_name as Category
                , vd.video_id as Film
                , COUNT(vt.votes) as Votes
            from Category c
                inner join Video vd on vd.category_id = c.category_id
                inner join Votes vt on vt.video_id = vd.video_id
            where c.category_id = 10 -- HipHop
            group by c.category_name
                , vd.video_id
        union
        select TOP 3 c.category_name
                , vd.video_id
                , COUNT(vt.votes) as Votes
            from Category c
                inner join Video vd on vd.category_id = c.category_id
                inner join Votes vt on vt.video_id = vd.video_id
            where c.category_id = 11 -- Rap
            group by c.category_name
                , vd.video_id
        -- You need as much UNION as there are categories.
    ) Top3VotesPerCategory
    group by Category
        , Film

Depending whether you accepte downvotes on a film, one could register a negative vote a film, and this wouldn't mean that he recommends this video. If you do support such functional feature, then you might want to consider a conditional SUM(), instead of a COUNT(), for the number of votes, so that negative votes would be considered and would decrease its overall points in regards of votes.