MAX() Aggregation Problem

[Repton]

I am trying to join three tables using the following query:

$query_SCORES = "SELECT
MAX(_hsSCORES.Totalscore) as Awin,
date_format(_hsSCORES.Date, '%d.%m.%Y') as SDate,
_hsSCORES.Featurewin,
_hsSCORES.Gameversion,
_hsUSERS.Nickname,
_hsUSERS.Country,
_Gameslist.Gamename,
_hsSCORES.Userlink,
_hsUSERS.Usernumber,
_hsUSERS.Email,
FROM _hsSCORES, _hsUSERS, _Gameslist
WHERE _hsSCORES.Userlink = _hsUSERS.Usernumber
AND _hsSCORES.Gametype = _Gameslist.Gametype
AND _hsSCORES.Subgametype = _Gameslist.Subgametype
GROUP BY _Gameslist.Creationnumber
ORDER BY _Gameslist.Gamename";

on MySQL version 3.2 (Host won't upgrade).

The query is designed to get the highest score for every type of game, and then get that users details.

The highest score in the _hsSCORES table is calculated properly, and the game name is retrieved properly from _Gameslist table for all games listed, but all of the other data is retrieved not for the highest score record in the _hsSCORES table, but for the lowest, in every single case (from _hsUSERS).

Is there a way to correctly perform this whole thing without running more than one query?

 

[swampBoogie]

You can't do that in Mysql with one query

http://www.mysql.com/doc/en/example-Maximum-column-group-row.html

Your query as written is invalid. It should not be allowed by Mysql. When using group by all expressions in the select list that is not the argument of an aggregate function should be present in the group by clause.