Pulling Unique Records

[connex]

Okay, to try explain the problem, I think I should first show the tables:

 

Score Table:

	ScoreID	GameID	BoardID	UserID	Score

 

Leaderboard table:

No	ID	GameID	BoardID	Title	DataType	Sort	ScoreLabel

 

Game Table:

	ID	GameName	GameSlug	GameDesc	GameW	GameH	GameFile	GameThum	GameCat	ScoreEnabled	DateAdded

 

 

Okay the tables look a bit messy, and yes, I do need 3 tables (I'm using external API's so I have to make sense of the data as best as I can).

 

Now the problem lies when I'm trying to extract the highscores. Once again, heres the current SQL I've managed to write up with my little knowledge and a lot of googleing:

			// get query
			$query = "SELECT * FROM scores, games, leaderboard ";
			$query.= "WHERE scores.GameID = leaderboard.GameID ";
			$query.= "AND leaderboard.ID = games.ID ";
			$query .= "ORDER BY ScoreID DESC";
			$query .= " LIMIT 10";

 

This displays all the records, and with PHP I can display everything about every highscore recorded.

Now what I'm trying to get the SQL to do is only display the highest score from all the games.

 

I'm aware I can do that in PHP, storing every field in array, and then after processing it, but I was hoping it can be done in MYSQL itself. I've half had the max (fieldname) work in previous examples, but that output just the highest score from all games (regardless how many games they are, only 1 highscore is output). I've stumbled onto the DISTINCT function, but havn't got that to work (e.g. $query.= "AND (SELECT DISTINCT FROM scores.GameID) "; ) . I don't know if thats the right way to use it, but I havn't found a decent tutorial explaining how I can use that within the other statement.

 

Can anyone point me in the right direction? I'm sorry if this is a bit confusing, and if needed Ill try explain in more detail.

And before I forget, I'm using the version : 5.0.91-community-log

 

Thank you!

[fenway]

Highest score from all games, or each game?