SELECT Scores.Score, COUNT(Ranking.Score) AS RANK FROM Scores , ( SELECT DISTINCT Score FROM Scores ) Ranking WHERE Scores.Score <= Ranking.Score GROUP BY Scores.Id, Scores.Score ORDER BY Scores.Score DESC;
GROUP BY Scores.Id, Scores.Score ,the Scores.Score is not nessary
How about this?
Select sc.Score, (Select count(*)+1 from (select distinct (Score) from Scores) as uniqeScores where Score > sc.Score) as rank from Scores sc order by sc.Score desc;
SELECT sc.Score, (SELECT COUNT(*) FROM (SELECT DISTINCT (Score) FROM Scores) AS uniques WHERE uniques.Score >= sc.Score) AS rank FROM Scores sc ORDER BY sc.Score DESC ;
both sc and uniques are tmp tables and have distinct score ,so just count uniques.Score >= sc.Score
Agree. This is enough.
select A.Score, count(B.Score)
from Scores as A, (select distinct Score
from Scores) as B
where A.score <= B.Score
group by A.Id
order by A.Score desc;
For each score, decide the ranking by counting the number of distinct scores larger than it.
Yeah, for the row with the same Id, the Score of it must be the same and i have try these two ways, group by only id is efficient than group by two columns.
Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.