I experimented a bit with the SQL editor on this site: http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all and I think I know what’s going on. So you can try it yourself, I’ll copy the code I used there, which does the same thing with different names. The “ORDER BY Country” is added just so the output looks a little nicer.
SELECT Country, CustomerID,
(SELECT COUNT(*)
FROM Customers CustomersInner
WHERE CustomersInner.CustomerID < Customers.CustomerID
AND CustomersInner.Country=Customers.Country) + 1
AS customer_seq_num
FROM Customers ORDER BY Country;
What helped me was trying the subquery on its own. If you try to run
SELECT COUNT(*)
FROM Customers C
WHERE C.CustomerID < Customers.CustomerID
AND C.Country=Customers.Country
by itself, you get an error saying that there is no column “Customers.CustomerID”. What “FROM Customers C” does is not only create the alias, but it also makes the “Customers” reference invalid. Because the SQL interpreter could not find Customers in the subquery, it tries to search the next outer query for a valid reference, but if there is no outer query, it fails. (It’s like the symbol resolution in JavaScript, if you are familiar with that language.) Here, I added an alias to the outer query to make it easier to see this. The statement does the exact same thing.
SELECT Country, CustomerID,
(SELECT COUNT(*)
FROM Customers AS CustomersInner
WHERE CustomersInner.CustomerID < CustomersOuter.CustomerID
AND CustomersInner.Country=CustomersOuter.Country) + 1
AS customer_seq_num
FROM Customers AS CustomersOuter ORDER BY Country;
If anyone who knows SQL more confidently than me still wants to weigh in on this, I’d appreciate it.