178. Rank Scores


  • 1
    K

    If its sql Server database then one can achieve it by simply using DENSE_RANK() window function.

    select score,DENSE_RANK() over(order by score desc) as Rank from Scores
    

  • 0
    T

    dense rank is not working


  • 0
    U

    here is the simple solution using 2 variables. MySQL doesn't have the rank function so have to use variable and initialize it. for oracle, SQL server one can simply use rank functionality
    Below is my sql query :

    select Score,
    @r := @r + (@p <> (@p := score)) as Rank
    from Scores, (select @r := 0,@p :=-1) temp
    order by Score des


  • 0
    J

    @tanyajain1112 why dense rank is not working?


  • 0
    H

    select Score ,count(distinct Score) as Rank from Scores
    order by Score desc


  • 0
    H

    select Score ,count(distinct Score) as Rank from Scores
    order by Score desc


  • 1
    S

    select Scores.Score, x.Rank
    from Scores
    left join
    (select A.Score as Score, count(distinct B.Score) as Rank
    from Scores A, Scores B
    where A.Score <= B.Score
    group by A.Score) as x
    on Scores.Score = x.Score
    order by Scores.Score DESC


  • 0
    W
    select  Score, count(*) as Rank
    from 
    (
              select   distinct a.score as t_score, b.id, b.score
                   from scores a , scores b
                   where a.score >= b.score
    ) aa
    group by id, score
    order by score desc
    

  • 0
    P

    SELECT Score, DENSE_RANK() OVER(ORDER BY Score DESC) AS Rank
    FROM Scores


  • 0
    M

    This one will surely work, enjoy and please pray for me for my interview tomorrow :)

    Solution by danniel:

    select S2.Score as Score, cast(S3.Rank as UNSIGNED) as Rank
    from Scores S2
    left join (
    select S.Score as Score,
    @curRank := @curRank+ 1 as Rank
    from (select distinct Score from Scores) S, (select @curRank := 0 ) R
    order by S.Score desc
    ) S3
    on S2.Score = S3.Score
    order by S3.Rank asc


Log in to reply
 

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