Databases Reference
In-Depth Information
| 106 | 35 |
| 60 | 35 |
| 13 | 35 |
| 158 | 35 |
+----------+-----+
Now let's add the rank, which should be the same for all the actors who played in 35
movies. We use three variables to do this: one to keep track of the current rank, one to
keep track of the previous actor's movie count, and one to keep track of the current
actor's movie count. We change the rank when the movie count changes. Here's a first
try:
mysql>
SET @curr_cnt := 0, @prev_cnt := 0, @rank := 0;
mysql>
SELECT actor_id,
->
@curr_cnt := COUNT(*) AS cnt,
->
@rank := IF(@prev_cnt <> @curr_cnt, @rank + 1, @rank) AS rank,
->
@prev_cnt := @curr_cnt AS dummy
->
FROM sakila.film_actor
->
GROUP BY actor_id
->
ORDER BY cnt DESC
->
LIMIT 10;
+----------+-----+------+-------+
| actor_id | cnt | rank | dummy |
+----------+-----+------+-------+
| 107 | 42 | 0 | 0 |
| 102 | 41 | 0 | 0 |
...
Oops—the rank and count never got updated from zero. Why did this happen?
It's impossible to give a one-size-fits-all answer. The problem could be as simple as a
misspelled variable name (in this example it's not), or something more involved. In this
case,
EXPLAIN
shows there's a temporary table and filesort, so the variables are being
evaluated at a different time from when we expected.
This is the type of inscrutable behavior you might experience with MySQL's user-
defined variables. Debugging such problems can be tough, but it can really pay off.
Ranking in SQL normally requires quadratic algorithms, such as counting the distinct
number of actors who played in a greater number of movies. A user-defined variable
solution can be a linear algorithm—quite an improvement.
An easy solution in this case is to add another level of temporary tables to the query,
using a subquery in the
FROM
clause:
mysql>
SET @curr_cnt := 0, @prev_cnt := 0, @rank := 0;
->
SELECT actor_id,
->
@curr_cnt := cnt AS cnt,
->
@rank := IF(@prev_cnt <> @curr_cnt, @rank + 1, @rank) AS rank,
->
@prev_cnt := @curr_cnt AS dummy
->
FROM (
->
SELECT actor_id, COUNT(*) AS cnt
->
FROM sakila.film_actor
->
GROUP BY actor_id
->
ORDER BY cnt DESC
