MySQL Select Rank

Question

I have a competition where reps can many different stores, stores have points based on people entering the store.

What I need to do is select the rank of a store (where id = x), based on the points value, if a store is tied, then the rank should be the same (i.e. two stores in joint first)

I then need to select the rank of a rep, based on the sum of the reps stores scores.

Stores

id  name    points  rep_id
1   Regents Street  1501    3
2   Oxford Street   1500    2
3   Carnaby Street  1499    3
4   Edward Street   1499    1
5   Edward Street   1499    4

Reps

id  name
1   John Smith
2   John Doe
3   Joe Smit
3   Lou Jackson

Store leaderboard:

1st - 1, Regents Street
2nd - 2, Oxford Street
3rd - 3,4,5

Rep leaderboard:

1st - 3, Joe Smit (3000)
2nd - 2, John Doe (1500)
3rd - 1,4 (1499)

I have a query to get the store ranks, but it doesn't work with ties.

    SELECT id,
       name,
       points,
       FIND_IN_SET(points,
                     (SELECT GROUP_CONCAT(points
                                          ORDER BY points DESC)
                      FROM stores)) AS rank
FROM stores
WHERE id = 1

Thanks

Answer 1

I would normally use variables for this type of ranking:

select s.*,
       (@rn := if(@p = points, @rn,
                  if(@p := points, @rn + 1, @rn + 1)
                 )
       ) as rank
from stores s cross join
     (select @rn := 0, @p := -1)
order by s.points desc;

Another approach is a correlated subquery:

select s.*,
       (select count(distinct s2.points)
        from stores s2
        where s2.points >= s.points
       ) as rank
from store s;

Both these queries actually implement a dense_rank() rather than a rank(). It is unclear from your question what you actually want. And, they can be modified for a rank as well.

EDIT:

If you need this for reps rather than stores (which does seem to be buried near the beginning of the question):

I think this will work in MySQL:

select s.rep_id,
       (select count(distinct r2.points)
        from (select s.rep_id, sum(s.points) as points
              from stores s
              group by s.rep_id
             ) r2
        where r2.points >= r.points
       ) as rank
from (select s.rep_id, sum(s.points) as points
      from stores s
      group by s.rep_id
     ) r;