MySQL name binding?

Question

SELECT Score, (SELECT COUNT(*) FROM (SELECT DISTINCT Score FROM Scores WHERE Score >= s.Score) AS tmp) AS Rank
FROM Scores s
ORDER BY Score DESC

Can anyone tell me why s.score column unknown to the innermost subquery?

Answer 1

That correlated subquery ("tmp") is too deeply nested, in effect the details of "tmp" are not known to the outer query:

SELECT
      Score
    , (
            SELECT
                  COUNT(*)
            FROM ( ------------------------------
                  SELECT DISTINCT
                        Score
                  FROM Scores
                  WHERE Score >= s.Score
            ) AS tmp ----------------------------
      )
      AS rank
FROM Scores s
ORDER BY
      Score DESC
;

You could simply use count(distinct score) and just remove the unnecessary layer. However also note that it is imperative to be very precise about which columns are being used for what purpose, so make sure you preface EVERY column reference with a table alias or table name if no alias has been defined. No exceptions.

SELECT
      s.Score
    , (
            SELECT
                  COUNT(DISTINCT s2.Score)
            FROM Scores s2
            WHERE s2.Score >= s.Score
      )
      AS rank
FROM Scores s
ORDER BY
      s.Score DESC
;

ps: rank() is a function name, so I'm not really keen on using "rank" as a column name as it can get confusing.

----

for MS SQL Server (sorry, misread the tags, but leaving it for other readers)

I would encourage avoid using correlated subqueries in the select clause, it is more efficient to do this within the from clause and the apply operator available in MS SQL Server is ideal for this:

SELECT
      s.Score
    , ca.[rank]
FROM Scores s
CROSS APPLY (
      SELECT
            COUNT(DISTINCT s2.Score) AS [rank]
      FROM Scores s2
      WHERE s2.Score >= s.Score
      ) ca
ORDER BY
      s.Score DESC
;