MySQL query problem

[kicktisho]

Hi all,
I'm not so experienced at all and i have difficulties writing a complicated query. After taking me whole yesterday i decided not to lose any more nerves, but to ask for help

here is the table.

CREATE TABLE trivia_results (
player_id int(10) unsigned NOT NULL,
points smallint(5) unsigned NOT NULL ,
game_date_end int(10) unsigned NOT NULL,
answered_questions tinyint(3) unsigned NOT NULL
)

in this table i keep records for player results for a trivia game.
game_date_end is for PHP timestamp. A sampe row is

player_id points game_date_end answered_q
1 625 1036952313 5


So the query i can't write must make a standing. It should take the last 10 games average points for a player and to
order the players in a standing proper way.


if somebody can help me, i'll greatfull.
Best Regards Ivan Kostov

 

[nigelcohen]

Ivan

Your query is perhaps too ambitious. I don't think it can be done with one query. The problem is trying to use only the last 10 games points for a player, and not all.

One possible solution is to use a program to drive the multiple MySQL queries. (We use Python which would work easily. I believe PHP also can acheive something similar).

Firstly, extract the last 10 results for a particular player. Once you have a result set with a limited number of records. You might achive that with a query like:

select *
from trivia_results
order by game_end_date desc
limit 10

You would need to "store" this set somewhere. You could set up a temporary table and use, instead of the basic "select.. " statement above, a "select. .. into .. " statement
eg
select * into TEMP
from trivia_results
order by game_end_date desc
limit 10

Then you can use a simple query to do your standings/arithmetic, eg.

select player_id, avg(points)
from trivai_results
group by player_id

 

[kicktisho]

10x for the help.
I'll try now differnet approach.
I thought the problem is in me, that i can't figure the query but now i feel better.

Best regards Ivan Kostov