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
My solution is strait forward:
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?
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.
Some lazy evaluation at work w/ solution 1. Look it up if it doesn't make sense. Cleared things up for me.
insert group by m1.Id before order by, then have a try
@Econo You mean the
@prev <> (@prev := Score)? I don't know whether/how it's defined, I only tried it and it worked here.
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?
@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.
@StefanPochmann why do we have to use group by id in this case?
@StefanPochmann Can you please explain this line? I'm trying to understand its meaning.
(@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.
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
@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.
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;
Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.