Ordering returns from a select statement based on the number of times a particular value appears

I have a general question that I would like to ask about MySQL and using the ORDER BY command in a SELECT statement. In particular, I want to know whether it is possible to count the number of times a particular value appears in a database and order the database based this count statement. For example, if I have a player table with the following player_ids:

player_id
1
2
3
4
5

and a game table with the following game and player ids:

game_id player_id
0       5
1       2
2       5
3       1
4       3
5       4
6       3
7       5
8       4
开发者_如何学编程

I want to be able to select the players from the player table and organise them by the number of times that they appeared in the database. For example, this would return player 5 first (as player_id has played the most number of games) followed by players 3 and 4. Is there anyway that I can do this? At the same time, I do not want duplicate records (for example, the record for player 5 only has to be returned once rather than 3 times). If anybody could help, it would be greatly appreciated.

You need to select the ID against a COUNT of the player_id's and use 'GROUP BY' to ensure that the counts are done against each individual ID. If you want to add up results instead of count them, then use SUM instead with GROUP BY. To order the whole lot by the number of results, use ORDER BY and use the same COUNT command as before.

Your statement will look something like this:-

select id, count(id) from table1, table2 where table1.id = table2.keyid group by id order by count(id) descending;

Try this:

SELECT p.*, COUNT(g.game_id) tot_games
FROM players p INNER JOIN game g
ON p.player_id = g.player_id
GROUP BY p.player_id
ORDER BY tot_games DESC