I didn’t try, but I would expect something like this to at least give the correct result (but of course the JOIN solutions are much cleaner anyway) :

SELECT NULL AS rank, NULL AS Code, NULL AS Name, NULL AS Population
FROM DUAL WHERE (@rank := 0)<0
UNION ALL
(SELECT @rank := @rank + 1 AS rank, Code, Name, Population
FROM Country ORDER BY Population DESC LIMIT 10)

select @rank:=COALESCE(@rank,0)+1 AS rank, Code, Name, Population FROM Country ORDER BY Population DESC LIMIT 10;
+------+------+--------------------+------------+
| rank | Code | Name               | Population |
+------+------+--------------------+------------+
|    1 | CHN  | China              | 1277558000 |
|    1 | IND  | India              | 1013662000 |
|    1 | USA  | United States      |  278357000 |
|    1 | IDN  | Indonesia          |  212107000 |
|    1 | BRA  | Brazil             |  170115000 |
|    1 | PAK  | Pakistan           |  156483000 |
|    1 | RUS  | Russian Federation |  146934000 |
|    1 | BGD  | Bangladesh         |  129155000 |
|    1 | JPN  | Japan              |  126714000 |
|    1 | NGA  | Nigeria            |  111506000 |
+------+------+--------------------+------------+

I think you may have tried your query in an already “used” client.
See, if I then run the same query again, I get:

+------+------+--------------------+------------+
| rank | Code | Name               | Population |
+------+------+--------------------+------------+
|    2 | CHN  | China              | 1277558000 |
|    3 | IND  | India              | 1013662000 |
|    4 | USA  | United States      |  278357000 |
|    5 | IDN  | Indonesia          |  212107000 |
|    6 | BRA  | Brazil             |  170115000 |
|    7 | PAK  | Pakistan           |  156483000 |
|    8 | RUS  | Russian Federation |  146934000 |
|    9 | BGD  | Bangladesh         |  129155000 |
|   10 | JPN  | Japan              |  126714000 |
|   11 | NGA  | Nigeria            |  111506000 |
+------+------+--------------------+------------+

Regards,
Shlomi

SELECT
@rank := IFNULL(@rank,0)+1 AS rank,
Code, Name, Population
FROM Country ORDER BY Population DESC LIMIT 10;

If you use COALESCE instead of IFNULL, for the same purpose you were trying to achieve, you would have achieved it!

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce

select @rank:=COALESCE(@rank,0)+1 AS rank,
Code, Name, Population
FROM Country ORDER BY Population DESC LIMIT 10;

Just a nitpick – a “rank” is commonly understood as assigning a grade corresponding to the sorting order of the attribute *value* – not necessarily the same as the occurrence.

In the case you present, the top 10 countries by population all have a different value for population, so you get to assign a unique number. But what if two (or more) of the top 10 happen to have the same value for population?

Well, the common notion for rank is that it should assign a number the occurrence of distinct values – not the occurrence of rows. This means that the same rank can appear if the population value happened to be the same.

What you are doing here is number the rows in order of appearance by population.

Here’s a good explanation of the difference between rank and ordered numbering:

http://technology.amis.nl/blog/189/analytical-sql-functions-theory-and-examples-part-2-on-the-order-by-and-windowing-clauses

kind regards,

ROland Bouman

“Subqueries in the FROM clause are executed even for the EXPLAIN statement (that is, derived temporary tables are built). This occurs because upper-level queries need information about all tables during the optimization phase, and the table represented by a subquery in the FROM clause is unavailable unless the subquery is executed.”

So, it sounds like the derived query will be executed before the optimizer even creates the plan for the outer query, assuming the outer query references tables.

That page also has an interesting example demonstrating how this side effect can result in the modification of table data if you do an EXPLAIN SELECT when the subquery contains a call to a stored procedure.

Toby – do you have an example?

Shlomi

SELECT @rank := @rank+1 AS rank, Code, Name, Population
FROM Country, (SELECT @rank := 0) d
ORDER BY Population DESC LIMIT 10;