SQL alias inside a Count

I’m trying to learn some SQL, and I feel like I should be able to do this, but I get a syntax error.

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

It allows me to do

# Write your MySQL query statement below
SELECT Score s,
  (SELECT COUNT(DISTINCT Score) FROM Scores WHERE Score >= s) AS Rank
FROM Scores
ORDER BY Score DESC

but for the question the first column has to be named Score.

I’ve seen other answers for this problem that seem really complicated, but if anyone has a simple way to make this idea work I’d appreciate it.
Thanks!

Answer

I think you want:

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;

Note that I aliases both table references and all column references.

Of course, this is simpler:

select s.score,
       dense_rank() over (order by s.score desc) as rank
from scores s
order by s.score desc;