Solution by YL66


  • 0
    L

    Description

    Table: Scores

    Id Score
    1 3.50
    2 3.65
    3 4.00
    4 3.85
    5 4.00
    6 3.65

    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.

    NOTE:

    1. order by Score desc.
    2. The Rank is consecutive.

    Solution


    Approach: Using DISTINCT [Accepted]

    Algorithm

    1. Find all the Score in the Scores table.

      SELECT DISTINCT Score FROM Scores;
      -- tmp
      
    2. COUNT the Score in the Scores table.

      SELECT count(*) FROM tmp WHERE tmp.Score >= s1.Score
      -- rank
      

    3. Associate and get the result

      SELECT Score, rank AS 'Rank' FROM Scores ORDER BY Score DESC;
      -- See the following
      

    MySQL

    SELECT
    	s1.Score,
    	(
    		SELECT
    			count(*)
    		FROM
    			(
    				SELECT DISTINCT
    					s2.Score
    				FROM
    					Scores s2
    			) tmp
    		WHERE
    			tmp.Score >= s1.Score
    	) AS 'Rank'
    FROM
    	Scores s1
    ORDER BY
    	s1.Score DESC;
    -- 10 / 10 test cases passed.
    -- Runtime:956 ms
    

Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.