Rewrite WITH statements in subqueries in SQL?

Question

I have the following two relationships:

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

Where Game.id is the primary key and where Devs .gid is the foreign key for Game.id.

In the previous post I made here , another user was kind enough to help me create a query that finds all the games made with most developers making this game. His answer used the WITH statement, and I'm not very good at them. since I’ve been studying SQL for only a few weeks. Here's a working request:

 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) 

In order to get acquainted with SQL, I am trying to rewrite this query using a subquery, not a WITH statement. I used this Oracle documentation to help me figure it out. I tried to rewrite the request as follows:

 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 the same. However, when I try to run the second request, I get an error

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

Does anyone know why I can get this error or why these two queries will not be identical?

Answer 1

You will need to duplicate this subquery with the last of the sentences, for example:

 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 this:

 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 CTE you choose twice. But you cannot from a subquery. First statement

 WHERE GamesDevs.DevsCount 

is correct. But

 (SELECT MAX(DevsCount) FROM GamesDevs) 

incorrect because you can reuse a subquery. Choosing from cte that works as a view, so you can use both max and compare count

Answer 3

You can use the RANK window function in the subquery to find those entries that have the most individual developers per game:

 SELECT GameName, DevsCount FROM ( SELECT Game.name AS GameName, COUNT(DISTINCT Devs.pid) AS DevsCount, RANK() OVER (ORDER BY COUNT(DISTINCT Devs.pid) DESC) AS rnk FROM Game INNER JOIN Devs ON Game.id = Devs.gid GROUP BY Devs.gid, Game.name ) t WHERE t.rnk = 1 

This way you do not need to repeat the SELECT MAX(DevsCount) . You need to select only those records that have rnk = 1 .

You should also consider ANSI-standard SQL to indicate the exact type of JOIN operation you are using.

@Giorgi's answer, which suggests using SELECT TOP 1 WITH TIES , is a much cleaner solution to your original problem. I just made this post an alternative way to fix the subquery you are using.