Top User SQL Query With Categories?

Question

I'm trying to figure out how to get the top users in each category with a mysql query: My Users Table looks like this:

user_id,category_id ... and some other stuff

My Votes Table looks like this (each row is one positive vote): reciever_id ... and some other stuff

I was thinking that I need to first determine the unique votes by user_id like this:

reciever_id, votes
1 2
2 6

Then I could order that by the number of votes ... Then select the users.whatever,distinct(category_id) from users, (that query) WHERE users_id=that_queries_user.id

Anyways I'm probably obviously really confused as to how to write this query and any help would be greatly appreciated.

Answer 1

This will return you top 10 users:

SELECT  u.*,
        (
        SELECT  COUNT(*)
        FROM    votes v
        WHERE   v.receiver_id = u.user_id
        ) AS score
FROM    users u
ORDER BY
        score DESC
LIMIT 10

This will return you one top user from each category:

SELECT  u.*
FROM    (
        SELECT  DISTINCT category_id
        FROM    users
        ) uo
JOIN    users u
ON      u.user_id = 
        (
        SELECT  user_id
        FROM    users ui
        WHERE   ui.category_id = uo.category_id
        ORDER BY
                (
                SELECT  COUNT(*)
                FROM    votes v
                WHERE   v.receiver_id = ui.user_id
                ) DESC
        LIMIT 1
        )