Query problem with MySQL 5

[Tero Ahonen]

I have a small query problem that I cannot solve.

I have an result table which contain all game results

mysql> select * from result;
+----+---------------------+------------------+-----------+---------+-------+
| id | created | points | player_id | game_id | round |
+----+---------------------+------------------+-----------+---------+-------+
| 1 | 2006-06-13 12:52:30 | 3811.88333333333 | 4 | 3 | 0 |
| 2 | 2006-06-13 12:53:33 | 3804.55 | 4 | 3 | 0 |
| 3 | 2006-06-13 14:17:42 | 0 | 5 | 3 | 0 |
| 4 | 2006-06-15 00:12:29 | 3826.05 | 5 | 3 | 0 |
| 5 | 2006-06-15 00:13:37 | 1893.5 | 5 | 3 | 0 |
| 6 | 2006-06-15 00:14:12 | 1891.16666666667 | 5 | 3 | 0 |
| 7 | 2006-07-28 08:34:27 | 1913.05 | 1 | 3 | 0 |
| 8 | 2006-07-28 08:36:22 | 1865.46666666667 | 1 | 3 | 0 |
| 9 | 2006-07-28 08:38:11 | 0 | 1 | 3 | 0 |
| 10 | 2006-07-28 08:38:49 | 1919.38333333333 | 1 | 3 | 0 |
| 11 | 2006-07-28 21:36:25 | 0 | 6 | 3 | 0 |
| 12 | 2006-07-31 01:08:56 | 0 | 1 | 3 | 0 |
| 13 | 2006-07-31 02:01:55 | 1841.61666666667 | 1 | 3 | 0 |
| 14 | 2006-07-31 02:05:47 | 1922.73333333333 | 1 | 3 | 0 |
| 15 | 2006-07-31 02:08:12 | 1879.31666666667 | 1 | 3 | 0 |
| 16 | 2006-08-09 21:58:05 | 0 | 1 | 3 | 0 |
| 17 | 2006-08-09 21:58:13 | 0 | 1 | 3 | 0 |
| 18 | 2006-08-09 23:27:10 | 1579.23333333333 | 1 | 3 | 4 |
| 19 | 2006-08-11 06:17:44 | 0 | 1 | 3 | 5 |
| 20 | 2006-08-11 07:23:04 | 3748.96666666667 | 1 | 3 | 22 |
| 21 | 2006-08-11 07:28:26 | 3517.98333333333 | 1 | 3 | 23 |
| 22 | 2006-08-11 07:28:36 | 5258.61666666667 | 1 | 3 | 23 |
| 23 | 2006-08-11 07:28:41 | 6908.61666666667 | 1 | 3 | 23 |
| 24 | 2006-08-11 07:30:26 | 3782.01666666667 | 1 | 3 | 25 |
| 25 | 2006-08-11 07:30:31 | 5610.78333333333 | 1 | 3 | 25 |
| 26 | 2006-08-11 07:30:34 | 7397.65 | 1 | 3 | 25 |
| 27 | 2006-08-11 07:30:34 | 9173.66666666667 | 1 | 3 | 25 |
| 28 | 2006-08-11 07:30:35 | 10941.3333333333 | 1 | 3 | 25 |
| 29 | 2006-08-11 07:30:35 | 12701.3333333333 | 1 | 3 | 25 |
| 30 | 2006-08-11 07:30:36 | 14454.8166666667 | 1 | 3 | 25 |
| 31 | 2006-08-11 07:30:36 | 16202.3 | 1 | 3 | 25 |
| 32 | 2006-08-11 07:30:36 | 17944.2666666667 | 1 | 3 | 25 |
| 33 | 2006-08-11 07:30:39 | 19645.6833333333 | 1 | 3 | 25 |
| 34 | 2006-08-11 07:30:39 | 21340.9166666667 | 1 | 3 | 25 |
| 35 | 2006-08-11 07:30:40 | 23030.3166666667 | 1 | 3 | 25 |
| 36 | 2006-08-11 07:30:43 | 24666.1333333333 | 1 | 3 | 25 |
| 37 | 2006-08-11 07:30:44 | 26285.4333333333 | 1 | 3 | 25 |
| 38 | 2006-08-11 07:35:21 | 3875.53333333333 | 1 | 3 | 27 |
+----+---------------------+------------------+-----------+---------+-------+

I have a query which I use the read the position on one invidual player..

mysql> select count(*) from result where points >= (SELECT max(points) from result where game_id = 3 AND player_id = 4);
+----------+
| count(*) |
+----------+
| 18 |
+----------+

But this result is wrong . I would like to check the position of a player from distinct results set.

So with this query I can created the distict result list with max points on each player

mysql> select distinct player_id, max(points) as maxp from result group by player_id order by maxp desc;
+-----------+------------------+
| player_id | maxp |
+-----------+------------------+
| 1 | 26285.4333333333 |
| 5 | 3826.05 |
| 4 | 3811.88333333333 |
| 6 | 0 |
+-----------+------------------+

How can I join the which I use to read the position of a player with below query.

[todd runstein]

Tero,

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.

[Tero Ahonen]

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.

[Aiglee Castillo]

You can do it using a temporary table:

  create table temp (player_id int, maxp float, pos int AUTO_INCREMENT, PRIMARY KEY (pos));
insert into temp (player_id, maxp) (select player_id, max(points) as maxp from result group by player_id order by maxp desc);
select pos, player_id, maxp from temp order by pos asc;  

Then you can drop the table
;)

[todd runstein]

So, for game 3, would something like this work?

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?