MySQL help: Find Rank of a particular user on the number of its occurance in another table.

Question

I have two tables 'users' and 'invites'

Fields of users - id, name, email, ...

Fields of invites- email, invitedby, status

My Problems:

1. Find the rank list of users on 'invites' table where the top user will be the one who has the most number of (status='confirmed') invites.

2. Find the rank of a particular user say id=15 where it stands on the rank list of total umber of invites.

Tones of Thanks in advance

Answer 1

For the first,

SELECT invitedby as id,COUNT(*) as confirmed
FROM invites
WHERE status='confirmed'
GROUP BY invitedby

For the second, use the first as a subquery (or you could make a view out of it) and then check to see how many other ids have a greater value than the one for the user you're looking for:

SELECT COUNT(*) as rank FROM (
    SELECT invitedby as id,COUNT(*) as confirmed
    FROM invites
    WHERE status='confirmed'
    GROUP BY invitedby
) as s
WHERE s.confirmed > XXXXX

(and replace XXXXX with the confirm count of the user you're querying for - if you make the first query a view you can easily use another SELECT to look this up). This will give you a rank where the top item is 0, the second item is 1, and so on.