Select only the highest values ​​with respect to 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.

I want to write an SQL query that will find the game with the most people who worked on this game. I wrote the following query:

 SELECT Game.name, count(DISTINCT Devs.pid) FROM Game, Devs WHERE Devs.gid=Game.id GROUP BY Devs.gid, Game.name ORDER BY count(Devs.pid) DESC; 

This query fulfills my task in the sense that it returns all games in the ratio, sorted by the number of people who worked on each game, but I am trying to modify this query so that it does two things. Firstly, he should return the game only to the majority of people who worked on it, and two, if two games work on them, on which an equal number of people work, it should return both of these games. I know that if I replace the top line as follows:

 SELECT TOP 1 Game.name, count(DISTINCT Devs.pid) 

Then he fulfills my first goal, but if there are two games that have the largest number of people who worked on them, then she returns only one of these games. How can I modify this query to return all games with the most people who worked on it?

Answer 1

The task can be reduced to:

Give me all the rows from the original query with the maximum number of developers

This can be done using the WITH statement. The answer is as follows:

 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 ORDER BY count(Devs.pid) DESC ) SELECT * FROM GamesDevs WHERE GamesDevs.DevsCount = (SELECT MAX(DevsCount) FROM GamesDevs) 

Hope this helps.

Answer 2

Try this query:

  select g.name, count(d.gid) from game g join devs d on g.id=d.gid group by g.name having count(d.gid)= (select max (temp.cnt) from (select gid p, count(*) cnt from devs group by gid) temp) ; 

Answer 3

Just use the with ties syntax if this is the only thing that doesn't work:

https://msdn.microsoft.com/en-us/library/ms189463.aspx

 select top 1 with ties game.name, count(distinct devs.pid) from game join devs on devs.gid = game.id group by devs.gid, game.name order by 3 desc 

Also consider the modern syntax of the connection.

Answer 4

This should also work :)

 SELECT Game.name, count(DISTINCT Devs.pid) FROM Game, Devs WHERE Devs.gid=Game.id GROUP BY Devs.gid, Game.name Having count(DISTINCT Devs.pid) = Max(count(DISTINCT Devs.pid)) ORDER BY count(Devs.pid) DESC;