Comments on: SQL: Ranking without self join

By: Rubear

Rubear — Sat, 14 Feb 2015 23:09:06 +0000

Hi,

Stumbled across here while looking for a ranking solution. I also required the version where ties affect the ranking as described in comment 32. Shlomi’s solution is on the right lines it’s just the order needs to be swapped around for it to work properly as you want step evaluated after rank. Thanks Shlomi for posting this, it’s been a big help!

SELECT user_name, user_score,
@prev := @curr,
@curr := user_score,
@rank := IF(@prev = @curr, @rank, @rank + @step) AS rank,
@step := IF(@prev = @curr, (@step + 1), 1) AS step

FROM your_score_table_or_leaderboard_query,
(SELECT @curr := null, @prev := null, @rank := 0, @step := 1) as holders
ORDER BY user_score DESC

By: A simple query | my experiments

A simple query | my experiments — Fri, 06 Jun 2014 17:21:50 +0000

[…] attempt one : Using Self joins., group-by. Complexity : O(n2) Some genius came up with the idea to avoid the O(n2) costs involved with self joins. Lets modify it to suit our […]

By: shlomi

shlomi — Thu, 13 Oct 2011 18:07:36 +0000

@Nandhagopal,
You would use something like this:
@step := IF(@prev = @curr, @step+1, 1) AS step
@rank := IF(@prev = @curr, @rank, @rank+@step) AS rank
…
SELECT @step := 1

By: Nandhagopal

Nandhagopal — Thu, 13 Oct 2011 16:42:51 +0000

I am new to the world of sql, so I apologise if my doubt sounds childish… In your examples, if two people are tied for the second spot then the one following them is 3rd. In actual conventional ranking systems, we place that fellow 4th. So, how would you change your query to incorporate it such that if m people are tied for the n’th rank, then the next person after them must be ranked (m+n).

By: Subrata

Subrata — Tue, 12 Apr 2011 09:13:04 +0000

Try this one

SELECT
s1.score_id, s1.student_name, s1.score, COUNT(DISTINCT s2.score) AS rank
FROM
score s1 JOIN score s2 ON (s1.score <= s2.score)
GROUP BY s1.score_id ORDER BY rank

By: bigcode

bigcode — Sat, 11 Dec 2010 15:36:00 +0000

I don’t have an example of doing a rank without the self join but my example does allow for grouping.
http://bigcode.wordpress.com/2010/12/11/5/

By: shlomi

shlomi — Tue, 12 Oct 2010 05:32:46 +0000

@Mark, Was taught that a couple of years ago here: https://shlomi-noach.github.io/blog/mysql/dynamic-sequencing-with-a-single-query#comment-63

By: Mark Plutowski

Mark Plutowski — Mon, 11 Oct 2010 20:44:47 +0000

@shlomi: thanks for the neat trick of using cross join to initialize the variables.

@GSnyder : this matters when using a tool (e.g., BI or reporting tool) that only allows entering a single SQL statement.

@Jay: as you say: “Now try your trick when you need a GROUP BY” — Indeedy, unfort’y this is the showstopper for me too. I can’t get it to work with a GROUP BY.

But hey, I still got one neat trick out of this, so thanks again for that Shlomi !

By: links for 2010-06-25 | Digitalistic - Mashup or die trying

links for 2010-06-25 | Digitalistic - Mashup or die trying — Fri, 25 Jun 2010 14:03:51 +0000

[…] SQL: Ranking without self join | code.openark.org (tags: sql join ranking optimization mysql) […]

By: shlomi

shlomi — Tue, 02 Feb 2010 19:53:03 +0000

George,

Reset as in @c := 0, nothing fancy…