I'd love to help you with this - SQL problems are always fun to solve - but I don't get what you're trying to do.
First of all, 18 is correct. What answer were you expecting?
Second, I don't understand the join you're trying to do.
Perhaps it would help if you provided the results you're trying to get out of the table. Maybe come up with the resultset you're looking for - then, perhaps we can help you write the SQL to produce the resultset.
Correct result should be 3, because only best results is counter from each player. Se the query to get players position in list should be executed agains result set created with something like this: "select * from result group by player_id order by points desc"
So first I need results list which cointain only best result from each player and from there I need to read players position in list.
select player_id, max(points) as points from result where game_id = 3 group by player_id order by points;
My "order by" statement may not work in your db as is, but does the general idea work? This will list all the players who played game 3, and put them in order from most points to least. Is that closer to what you're looking for?
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop