GROUP,ORDER BY - Sort first, Multi table

[Misspell]

Ok, heres my story.

End result:
Print out a table (really two) that shows the high scores for games.

I have two tables that im working with.
One has the names of the games in it (games_list), the the other holds the scores (games_scores).

games_list
'gid' (games id)
'gtitle' (game name)
'gname' (name of the games file)
'active' (game active or not)

games_scores
'gid' (games id)
'mid' (members id)
'name' (members name)
'score' (the score)

Like i said above i want to print out a list of the high scores for each game.

Heres the SQL's i've have tried, but none have worked correclty. Im using the GROUP BY and ORDER BY but it seams to be grouping the query before its sorting (ordering) it and not returing me the high score but the first score for the game. I can not change the table structures because this is an add-on for a fourm arcade mod.

This one works fine, but it pulls all the records.
It shows multi-members scores for the same game.

        $DB->query("SELECT s.mid, s.gid, s.score as high_score, s.name, g.gtitle, g.gname
                      FROM ibf_games_scores s, ibf_games_list g  
                    WHERE g.active='1' and g.gid=s.gid ORDER BY s.gid, high_score DESC "); 

The rest of these give me the same results.
They returns a list of all the games grouped correctly (one game, one score) but does not give me the highest score for the game.

        $DB->query("SELECT s.mid, s.gid, s.score as high_score, s.name, g.gtitle, g.gname
                      FROM ibf_games_scores s, ibf_games_list g  
                    WHERE g.active='1' and g.gid=s.gid GROUP BY s.gid ORDER BY s.gid, high_score DESC "); 

Changed: s.gid to s.gid as game_id and also changed the GROUP BY game_id

        $DB->query("SELECT s.mid, s.gid as game_id, s.score as high_score, s.name, g.gtitle, g.gname
                      FROM ibf_games_scores s, ibf_games_list g  
                    WHERE g.active='1' and g.gid=s.gid GROUP BY game_id ORDER BY s.gid, high_score DESC "); 

Added the MAX(s.score)

        $DB->query("SELECT s.mid, s.gid as game_id, MAX(s.score) as high_score, s.name, g.gtitle, g.gname
                      FROM ibf_games_scores s, ibf_games_list g  
                    WHERE g.active='1' and g.gid=s.gid GROUP BY game_id ORDER BY s.gid, high_score DESC "); 

Changed to LEFT JOIN

        $DB->query("SELECT s.mid, s.gid as game_id, MAX(s.score) as high_score, s.name, g.gtitle, g.gname
                      FROM ibf_games_scores s 
                    LEFT JOIN ibf_games_list g ON (g.gid=s.gid)
                    WHERE g.active='1'
                    GROUP BY game_id
                    ORDER BY high_score DESC"); 

And im sure there where a few others i tried... im goning cross eyed lookin at this damm thing

Ive tried it with only on table, the games_scores and still nothing.

Can somone please lead me in the right direction, or toss out some suggestion of doing this with out haveing to pull all the records form the games_scores table. After time goes by, the table gets big and i do not want to pull all kinds of rows that i do not need.

Thanks for any help.

[phpman]

it will pull all of them that have active = 1

$DB->query("SELECT s.mid, s.gid, s.score as high_score, s.name, g.gtitle, g.gname 
                      FROM ibf_games_scores s, ibf_games_list g   
                    WHERE g.active='1' and g.gid=s.gid ORDER BY s.gid, high_score DESC "); 

that shouldhave worked but if you have all the column names "active" set to 1 then all of them will show? how many doyouhave set to 1?

[Misspell]

Thanks for the responce Mr. PHP

The active='1' part is working correctly.

Let me restate my problem with less words.

I have a table that holds all the players scores for 10 different games (we'll say 1,000 entries in all).
Im looking for a query that will pull only the high score for each game.

I could just pull all the records then sort them out later, but i do not want to pull 1,000 scores, cause that just dont make sence when the query should bebale to sort them for me and return only 10 (the high score for each game).

Like i said above, it seams like the query is doing the GROUP BY and then doing the ORDER BY . I need it to do the opposite.


this seams lik eit shuold work for me, but its not...

$DB->query("SELECT s.mid, s.gid as game_id, MAX(s.score) as high_score, s.name, g.gtitle, g.gname
                      FROM ibf_games_scores s, ibf_games_list g  
                    WHERE g.active='1' and g.gid=s.gid GROUP BY game_id ORDER BY s.gid, high_score DESC "); 

Hummm... somhting just poped into my head... maybe if i take the s.gid out of the ORDER BY, it will work... My server is down so i cant try it right now... but i will and post back if that works or not.

[Misspell]

I tried the SQL i posted in my last post woth out the s.gid in the ORDER BY and it returned one score for each game, but it was not the highest score for the games.

[phpman]

so you have more than 1 game_id for each user? so 1 game can like have 2 high scores and you want it to list the highest one?

well this should have worked like I said before. but if you are not gettign the results you wanted then it maybe the way you have it structured.

what specifies the difference between each game?

$DB->query("SELECT s.mid, s.gid as game_id, MAX(s.score) as high_score, s.name, g.gtitle, g.gname
FROM ibf_games_scores s, ibf_games_list g  
WHERE g.active='1' and g.gid=s.gid GROUP BY game_id ORDER BY s.gid, high_score DESC ");