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

try this (if, as you say, each row is one positive vote):

  Select User_id, category_id, Count(*) voteCnt
  From Users u Join Votes v 
     On v.receiver_id = u.user_id
  Group By User_id, category_id
  Having Count(*) > [Whayever Threshold you want]
  Order By Count(*) Desc

or this if the votes column is actually the count of votes:

  Select User_id, category_id, Sum(votes) voteSum
  From Users u Join Votes v 
     On v.receiver_id = u.user_id
  Group By User_id, category_id
  Having Sum(votes) > [Whayever Threshold you want]
  Order By Sum(votes) Desc

Answer 2

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
        )