SQL query inside max function

Question

I can't take the max value from a SQL query.

I wrote this SQL query:

SELECT P.p_name AS leadergame, COUNT(winner) AS wins
FROM game G
INNER JOIN player P ON (P.id = G.winner) 
GROUP BY winner;

and I get this result:

leadergame     wins
-------------------
Nick            2
Andrew          1
Alex            3

How can I take only the leadergame with the most wins?

Answer 1

The answer to your question relies heavily on the RDBMS that you are using

SQL Server

SELECT TOP 1 P.p_name AS leadergame, COUNT(G.winner) AS wins
FROM game G
INNER JOIN player P ON P.id = G.winner
GROUP BY P.p_name
ORDER BY COUNT(G.winner) DESC;

MySQL\Postgres

SELECT P.p_name AS leadergame, Count(winner) AS wins
FROM game G
INNER JOIN player P ON (P.id = G.winner) 
GROUP BY winner
ORDER BY wins DESC
LIMIT 1;

Or you can apply a SubQuery and achieve the results

SELECT P.p_name AS leadergame, Count(winner) AS wins
FROM game G
INNER JOIN player P ON (P.id = G.winner) 
GROUP BY winner
HAVING Count(winner) = (
    SELECT MAX(win_count)
    FROM (
        SELECT Count(winner) AS win_count
        FROM game
        GROUP BY winner
    ) AS max_wins
);