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.