Submit your SQL to Leetcode online judge: https://oj.leetcode.com/problems/rank-scores/
Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no “holes” between ranks.
+----+-------+ | Id | Score | +----+-------+ | 1 | 3.50 | | 2 | 3.65 | | 3 | 4.00 | | 4 | 3.85 | | 5 | 4.00 | | 6 | 3.65 | +----+-------+
For example, given the above Scores table, your query should generate the following report (order by highest score):
+-------+------+ | Score | Rank | +-------+------+ | 4.00 | 1 | | 4.00 | 1 | | 3.85 | 2 | | 3.65 | 3 | | 3.65 | 3 | | 3.50 | 4 | +-------+------+
The following SQL returns the distinct scores:
Select Distinct Score from Scores
Then we can count how many and compute the rank:
Select Count(1) + 1 From (Select Distinct Score from Scores) as uniqeScores where Score > sc.Score
And put it in the entire SQL:
Select sc.Score, (Select Count(1) + 1 From (Select Distinct Score from Scores) as uniqeScores where Score > sc.Score) as rank From Scores sc Order by sc.Score Desc;
This gives a pretty good accepted results:
–EOF (The Ultimate Computing & Technology Blog) —
GD Star Rating
loading...
296 wordsloading...
Last Post: How to Fix Visual Studio Search in Files Not Displaying List of Files Error?
Next Post: How to Call Win32 APIs in C++ code - Quick Tutorial
Hello! using this query how to rank this between 2 dates and by user_id?
omo. this is so helpful. Lots of love to you thank you
^_^