Selecting row with maximum value in field?

[Cep]

Hello,

I have written a query which will count the results of card games. I am basically counting how many times a player has been beaten by all the other players for every game they have played. I have don't this using a field called count.

I now have a record set but I only want the row or rows with the highest count value, is it possible to pick these out and leave the rest?

Here is my query

[code]
SELECT cw_results.player_id, cw_results.outby_id, cw_players.name, Count( cw_players_1.name ) AS count
FROM cw_players AS cw_players_1
RIGHT JOIN (
(
cw_games
RIGHT JOIN cw_results ON cw_games.game_id = cw_results.game_id
)
LEFT JOIN cw_players ON cw_results.outby_id = cw_players.player_id
) ON cw_players_1.player_id = cw_results.player_id
GROUP BY cw_results.player_id, cw_results.outby_id, cw_players.name
HAVING (
(
(
cw_results.player_id
) =1
)
AND (
(
cw_results.outby_id
) NOT LIKE 0
)
)
LIMIT 0 , 30;
[/code]

[fenway]

I'm having trouble parsing that myself... and right join?

[Cep]

I copied that from phpmyadmin after I had manually entered the sql and it ran ok. try this from my script,

[code]
SELECT cw_results.player_id, cw_results.outby_id, cw_players.name, Count(cw_players_1.name) AS count
          FROM cw_players AS cw_players_1 RIGHT JOIN ((cw_games RIGHT JOIN cw_results ON cw_games.game_id = cw_results.game_id) LEFT JOIN cw_players ON cw_results.outby_id = cw_players.player_id) ON cw_players_1.player_id = cw_results.player_id
          GROUP BY cw_results.player_id, cw_results.outby_id, cw_players.name
          HAVING (((cw_results.player_id)=1) AND ((cw_results.outby_id) Not Like 0))
[/code]

[fenway]

That HAVING should probably be WHERE... and "not like 0" could probablybe ">0"... if you want just the highest count, you'll have to wrap the whole thing again, and select MAX() of that.

[Cep]

Hi Fenway

thanks for the reply, is this what you meant for the corrected SQL?

[code]
SELECT cw_results.player_id, cw_results.outby_id, cw_players.name, Count(cw_players_1.name) AS count
          FROM cw_players AS cw_players_1 RIGHT JOIN ((cw_games RIGHT JOIN cw_results ON cw_games.game_id = cw_results.game_id) LEFT JOIN cw_players ON cw_results.outby_id = cw_players.player_id) ON cw_players_1.player_id = cw_results.player_id
          GROUP BY cw_results.player_id, cw_results.outby_id, cw_players.name
          WHERE MAX((((cw_results.player_id)=1) AND ((cw_results.outby_id) > 0)))
[/code][/code]

[fenway]

No, I mean that once you generate your counts, you'll need to use that derived table to find the max() for each one.