CODEWITHSUNDEEP
mysqlsql
AGE
AGE

Reputation: 3792

SQL - Selecting the most occurring value

I have a very simple database consisting of these tables. Here I am trying to write a query to select the player who played in the most games from the Games table.

Just by looking at the below example you can tell that the answer is Ben of course, because PID 2 (which is Ben from the Players table) appears the most times in the Games table, meaning Ben won the most games.

But I am not sure how to write the query for selecting the most recurring value in the Games table.

Players         Games          

PID | name      GID | PID
----|-----      ----|------
1   |  Al       A   |  3
2   |  Ben      A   |  2
3   |  Carl     A   |  2
4   |  Jack     A   |  1
5   |  Ron      A   |  4

Does anyone have any suggestions? Thanks!

Upvotes: 0

Views: 3308

Answers (2)

John Fink
John Fink

Reputation: 313

I'm guessing there's no counting to be done, just a simple integer value in the Games table indicating the number of games for a player ID...

SELECT pl.name FROM Players pl join Games g ON pl.NID=g.GID
ORDER BY g.games DESC
limit 1;

Upvotes: 1

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

Well, join players and games, count, group by player, order by count desc (greatest first), and limit to one if you want the first.

SELECT p.NID, p.name, COUNT(*)
FROM Players p
INNER JOIN Games g ON g.PID= p.NID
GROUP BY p.NID, p.name
ORDER BY COUNT(*) DESC
LIMIT 1;

Upvotes: 2

Related Questions