CODEWITHSUNDEEP
mysqlrank
nothotscott
nothotscott

Reputation: 65

SQL Ranking with multiple rows [Same users will still have same rank ]

I know a little bit of SQL and I've been wondering how to select users from my Users table and I've found this:

SELECT *, FIND_IN_SET( Score, (    
    SELECT GROUP_CONCAT( Score ORDER BY Score DESC ) 
        FROM Users
    )
) AS Rank
FROM Users
WHERE UserID = 100
ORDER BY Score DESC

The problem is that if there's multiple rows of the same user, then they will get ranked individually. I was wondering how to get the highest Score and have Rank return that

What it looks like(without the WHERE UserID = 100 clause):

ID-----UserID-----Score----Rank
1------100--------6--------1
2------192--------4--------2
3------192--------3--------3

What I want(without the WHERE UserID = 100 clause):

ID-----UserID-----Score----Rank
1------100--------6--------1
2------192--------4--------2
3------192--------3--------2

Thanks for the help!

Upvotes: 2

Views: 1218

Answers (3)

Blag
Blag

Reputation: 5894

Use a GROUP BY(UserID) with a MAX(Score) to get a maxScore and then rank with it.

(As OP Want every line of user but only the best rank we add an INNER JOIN)

SELECT TM.`UserID`, TM.`Score`, T3.`rank`
FROM `Users` as TM
INNER JOIN (
        SELECT `UserID`, FIND_IN_SET(
                Max(`Score`), (
                    select GROUP_CONCAT(
                        DISTINCT `Score`
                        order by `Score` DESC
                    )
                    From (
                        select MAX(`Score`) as `Score`
                        FROM `Users` as T0
                        GROUP BY `UserID`
                    ) as T1
                )
            ) as `rank`
        FROM `Users` as T2
        group by `UserID`
    ) as T3
    ON TM.`UserID` = T3.`UserID`
-- If we need to filter or sort, we make it here :
WHERE TM.`UserID` = 192
ORDER BY TM.`Score`

Upvotes: 0

Paul Spiegel
Paul Spiegel

Reputation: 31792

You can count every user with less score: 

SELECT u.ID, u.UserID u.Score, 1 + COUNT(*) as Rank -- 1 + count every user with less score
FROM Users u
JOIN Users loser
  ON loser.Score < u.score -- join with every user having less score
GROUP BY u.ID, u.UserID, u.Score
ORDER BY Rank

Upvotes: 0

Pablo Digiani
Pablo Digiani

Reputation: 602

Try the following:

SET @rank=0;
SELECT *, @rank:=@rank+1 AS rank
FROM Users
WHERE UserID = 100
ORDER BY Score DESC

Upvotes: 1

Related Questions