Calculate DENSE_RANK for MySQL

In this post, I will show you SQL query for calculateing dense rank for MySQL.
You know Oracle and SQL Server have the function called DENSE_RANK, which is for returning rank of the value. Unfortunately MySQL doesn't have the built-in function.

We can calculate dense rank by nested select for same table with count function. However this strategy is toooo slow because calculating rank by counting number of rows above the target row for every rows.

Anyway here is the query for calculating dense rank for MySQL.

SELECT id,
  @rnk:=IF(@preval <=> score, @rnk, @row + 1) AS dns_rnk,
  @row:= @row+1 AS rnk,
  @preval:=score as score
FROM table
# be careful for NULL handling.
# if all the values of score column are null, then dns_rank will zero.
# please set proper initial value for @preval based on your data.
JOIN (SELECT @rnk := 0, @preval :=null, @row := 0) r
ORDER BY score DESC

The result should be something like this.

id	dns_rnk	rnk	score
1	1	1	100
4	2	2	81
3	3	3	75
2	3	4	75
5	5	5	60

A few things I noticed while building above query.

• null handling as I commented on the above SQL. If all the values of score column is null and if you would like to get 1 for all their dns_rnk, you should set @preval = -1 (a bit depends on the range of the score value).
• You can add where clause to narrow down the target rows. However the following case the dns_rnk is unable to be calculated correctly.

SELECT t1.id,
  @rnk:=IF(@preval <=> t1.score, @rnk, @row + 1) AS dns_rnk,
  @row:= @row+1 AS rnk,
  @preval:=score as score
FROM table1 AS t1
JOIN (SELECT @rnk := 0, @preval :=null, @row := 0) r
# direct join with table1 is fine.
INNER JOIN table2 AS t2
ON t2.id_for_t1 = t1.id
# ---- this join breaks dense rank ----
INNER JOIN table3 AS t3
ON t3.id = t2.id_for_t3
# -------------------------------------
ORDER BY t1.score DESC

In this case, you should pick up t1.id which you would like to make ranking first, and then apply actual dns_rank calcuation.

SELECT t1.id,
   @rnk:=IF(@preval <=> t1.score, @rnk, @row + 1) AS dns_rnk,
   @row:= @row+1 as rnk,
   @preval:=t1.score as score
FROM table1 AS t1
JOIN (SELECT @rnk := 0, @preval :=null, @row := 0) r
WHERE t1.score IS NOT NULL
AND t1.id IN (
    SELECT t2.id_for_t1
    FROM table2 AS t2
    INNER JOIN table3 AS t3 ON t3.id = t2.id_for_t3
# you can add any narrow down condition for t1.id 
)
ORDER BY t1.score DESC;

I am not a wizard of MySQL, so if you think this post is something wrong, please feel free to put a comment...