0

I am trying to get the current position in a table in order to get the current ranking of a clan ingame.

I am searching a clan by its name, ordered by clan points, but have no clue how to get then its ranking.

SELECT clans.*, players.playername 
FROM `clans` 
LEFT JOIN players ON clans.leader_userid = players.userid 
WHERE clanname LIKE ? 
ORDER BY clans.points DESC LIMIT ?, ?

I would like to LEFT JOIN "AGAIN" this query with 

SELECT COUNT(1) WHERE clans.points >= clans.points

but I dont know how.

What would be the correct query ?

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
NoobyLearner
  • 109
  • 10
  • Possible duplicate of [How to perform grouped ranking in MySQL](https://stackoverflow.com/questions/532878/how-to-perform-grouped-ranking-in-mysql) – twoleggedhorse Sep 23 '17 at 07:57

2 Answers2

2

I don't fully understand what you are trying to do. But, you can put this query as a subquery, and join it like this:

SELECT clans.*, players.playername, COALESCE(sub.PointsCount) AS PointsCount
FROM `clans` 
LEFT JOIN players ON clans.leader_userid = players.userid 
LEFT JOIN
(
   SELECT points, COUNT(1) PointsCount
   FROM clans
   GROUP BY points
) as sub on sub.points >= clans.points
WHERE clanname LIKE ? 
ORDER BY clans.points DESC LIMIT ?, ?

Otherwise, you can use a correlated subquery like in this other answer.

1

You can use a correlated subquery for this:

SELECT c.*, players.playername,
       (SELECT COUNT(*) 
        FROM clans c2 
        WHERE c2.points >= c.points) + 1 AS rank           
FROM clans c
LEFT JOIN players ON c.leader_userid = players.userid 
WHERE c.clanname LIKE ? 
ORDER BY c.points DESC LIMIT ?, ?

For each clan the correlated subquery returns the number of clans having more points. If you add 1 to this number then you get the rank of the clan.

Note: If no clans are found by the subquery COUNT(*) will return 0 so using a COALESCE here is redundant.

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98