Rewriting WITH statements into subquery statements in SQL?

Question

I have the following two relations:

Game(id, name, year)
Devs(pid, gid, role)

Where Game.id is a primary key, and where Devs.gid is a foreign key to Game.id.

In a previous post I made here, another user was kind enough to help me out in creating a query that finds all games made with the most developers making that game. His answer used a WITH statement, and I am not very familiar with these as I'm only a few weeks into learning SQL. Here's the working query:

WITH GamesDevs (GameName, DevsCount)
AS
(
    SELECT Game.name AS GameName, count(DISTINCT Devs.pid) AS DevsCount
    FROM Game, Devs
    WHERE Devs.gid=Game.id
    GROUP BY Devs.gid, Game.name
)

SELECT * FROM GamesDevs WHERE GamesDevs.DevsCount = (SELECT MAX(DevsCount) FROM GamesDevs)

For the sole purpose of getting more familiar with SQL, I am trying to rewrite this query using a subquery instead of a WITH statement. I've been using this Oracle documentation to help me figure it out. I tried rewriting the query like this:

SELECT *
FROM (SELECT Game.name AS GameName, count(DISTINCT Devs.pid) AS DevsCount
    FROM Game, Devs
    WHERE Devs.gid=Game.id
    GROUP BY Devs.gid, Game.name) GamesDevs
WHERE GamesDevs.DevsCount = (SELECT MAX(DevsCount) FROM GamesDevs)

As far as I can tell, these two queries should be identical. However, when I try running the second query, I get the error

Msg 207, Level 16, State 1, Line 6 Invalid column name 'DevsCount'.

Does anyone know why I might be getting this error, or why these two queries wouldn't be identical?

Answer 1

You will need to duplicate that subquery in last from clause like:

SELECT *
FROM (SELECT Game.name AS GameName, count(DISTINCT Devs.pid) AS DevsCount
    FROM Game, Devs
    WHERE Devs.gid=Game.id
    GROUP BY Devs.gid, Game.name) GamesDevs
WHERE GamesDevs.DevsCount = (SELECT MAX(DevsCount) FROM (SELECT Game.name AS GameName, count(DISTINCT Devs.pid) AS DevsCount
    FROM Game
    INNER JOIN Devs ON Devs.gid=Game.id
    GROUP BY Devs.gid, Game.name))

But better do it like:

SELECT TOP 1 WITH TIES Game.name AS GameName, count(DISTINCT Devs.pid) AS DevsCount
FROM Game
INNER JOIN Devs ON Devs.gid=Game.id
GROUP BY Devs.gid, Game.name
ORDER BY DevsCount DESC

Answer 2

The problem is this line:

WHERE GamesDevs.DevsCount = (SELECT MAX(DevsCount) FROM GamesDevs)

From a CTE you select twice. But you can not from a sub query. The first statement

WHERE GamesDevs.DevsCount 

is correct. But

(SELECT MAX(DevsCount) FROM GamesDevs)

is not correct because you can reuse the subquery. Selecting from the cte which is acting like a view works that is why you can use both a max and compare the count