Simple, Short, Fast


  • 99

    These are four different solutions.


    With Variables: 841 ms

    First one uses two variables, one for the current rank and one for the previous score.

    SELECT
      Score,
      @rank := @rank + (@prev <> (@prev := Score)) Rank
    FROM
      Scores,
      (SELECT @rank := 0, @prev := -1) init
    ORDER BY Score desc
    

    Always Count: 1322 ms

    This one counts, for each score, the number of distinct greater or equal scores.

    SELECT
      Score,
      (SELECT count(distinct Score) FROM Scores WHERE Score >= s.Score) Rank
    FROM Scores s
    ORDER BY Score desc
    

    Always Count, Pre-uniqued: 795 ms

    Same as the previous one, but faster because I have a subquery that "uniquifies" the scores first. Not entirely sure why it's faster, I'm guessing MySQL makes tmp a temporary table and uses it for every outer Score.

    SELECT
      Score,
      (SELECT count(*) FROM (SELECT distinct Score s FROM Scores) tmp WHERE s >= Score) Rank
    FROM Scores
    ORDER BY Score desc
    

    Filter/count Scores^2: 1414 ms

    Inspired by the attempt in wangkan2001's answer. Finally Id is good for something :-)

    SELECT s.Score, count(distinct t.score) Rank
    FROM Scores s JOIN Scores t ON s.Score <= t.score
    GROUP BY s.Id
    ORDER BY s.Score desc

  • 1
    W

    My solution is strait forward:
    select m2.Score,
    count(distinct m1.Score)+1 as Rank
    from Scores m1, Scores m2
    where m1.Score > m2.Score
    order by Rank;

    How solve empty table problem?


  • 0

    I don't understand. That's not even close to a solution (it only returns one row). Are you talking about a different problem maybe?

    Also, please format your code as such so it's more readable. To do so, simply don't ignore the big fat red "Writing code?..." information when editing your post.

    Edit: Ok, I think I get what you meant to do. I added a working version to my solutions.


  • 0
    S

    Some lazy evaluation at work w/ solution 1. Look it up if it doesn't make sense. Cleared things up for me.


  • 0
    E

    Does the first solution have race condition problems?


  • 0
    E

    insert group by m1.Id before order by, then have a try


  • 0

    @Econo You mean the @prev <> (@prev := Score)? I don't know whether/how it's defined, I only tried it and it worked here.


  • 0
    K

    really brilliant!


  • 0
    C

    Brilliant solutions!


  • 0
    S

    Solution 1 is weird. As far as I know, Select clause should execute before Order by, as explained here, which means by the time all rows are ordered by score, every row should already been assigned a rank value with natural order like this:

    id, score, rank
    '1'| '3.5' | '1'
    '2' | '3.65' | '2'
    '3' | '4' | '3'
    '4' | '3.85' | '4'
    '5' | '4' | '5'
    '6' | '3.65' | '6'

    But instead, the result turns out that the order clause executed before select (table sorted first by score, then rank is calculated).

    So, is there any mechanism that changed the execution sequence?


  • 1

    @Seddas I think you're misinterpreting what Gordon said there. He said SELECT is parsed / interpreted before ORDER BY, so that in the ORDER BY part I can refer to something from the SELECT part. He didn't say that SELECT is executed first.

    I think doing it like that is pretty much a standard way. It's for example also used in this high-voted answer.


  • 0
    T

    @StefanPochmann why do we have to use group by id in this case?


  • 0
    Z

    @StefanPochmann Can you please explain this line? I'm trying to understand its meaning.


  • 0
    T

    @StefanPochmann said in Simple, Short, Fast:

    (@prev <> (@prev := Score))

    @StefanPochmann Can you explain how this works: (@prev <> (@prev := Score))? It seems that the first @prev stores the value of Score of the last row, while the latter one is assigned with the value of Score in the current row.


  • 0
    P

    hi Stefan,

    can you tell me why i am getting char type value in rank in below query and how can i solve it

    select score as score, @rank :=if (@prev = (@prev:=score) ,@rank,@rank+1) as rank
    from (select score from scores order by score desc) s, (select @rank :=0,@prev :=0) rnk


  • 0

    @pathak007 No, I don't understand why your ranks become strings. Shortest "fix" I found was to use @rank+0 in the true-part of your if. Though I don't really know why that works.

    Do you know whether it happens only on LeetCode or in MySQL in general? If it's in general, it might be a good question for StackOverflow.


  • 0
    F

    Hello , @StefanPochmann
    Thank you for your nice solutions.
    But when I write like follow , the rank in output is with VARCHAR type but not numeric type. can you tell me what causes that ?

    select 
        score, 
        case 
            when @prev = score then @rank
            when (@prev := score) OR 1 then @rank:=@rank+1 
        end as rank
    from 
        scores, 
        (select @prev:=NULL, @rank:=0) init
    order by score desc;
    

Log in to reply
 

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