How do I collate data joined from multiple tables for future use

[ephro]

Currently I have two tables:

Table 'tournament'
tourney_id
tourney_name
players
amount
---some other fields---

Table 'tournament_results'
tour_results_id <-- unique key
tourney_id <-- relates to tournament
tour_player_name
points
---some other fields---


I have no problem showing the results of finishes by selecting on tournament_results and summing points. However now I have over 6000 unique names in results, and want to page the data that comes out. I also want all players that would tie in points to be at the 'same' place for example two people could tie for second and the next place would be 4th. Thirdly, I would also like to know someones's total place by simply selecting their name without the calculation.

The first problem would be solved by my original query using limit and offset, however the second and third problems are giving my issues. The way I see of doing it in my head is to get all my data summed up and placing it in a new table, and that table is where all the information is selected from, is there a better way to do this besides running a script everytime I update or change the tournament_results table?

Thank you for your time.

 

[TonyGroves]

Assuming all the information is contained in the "tournament_results" table, and each player has several entries in that table. which are to be summed, then you could use:
[tt]
set @position=0;
select position,tour_player_name,t3.points
from
(
select
(@position:=@position+occurrences)-occurrences+1
position,
t2.*
from
(
# get no. of occurrences for each points count
select points,count(*) occurrences
from
(
# get no. of points for each player
select sum(points) points
from tournament_results
group by tour_player_name
)
t1
group by points
order by points desc
)
t2
)
t3
join
(
# get name and no. of points for each player
select tour_player_name,sum(points) points
from tournament_results
group by tour_player_name
)
t4
using(points)
order by position
[/tt]