SQL ranking database

[jonnyboy2]

I am attempting to write a ranking program that takes results in the form

Name | Club | Compulsory Routine | Difficulty | Total | Voluntary Routine | Difficulty | Total | Final Routine | Difficulty | Total | Overall Total
NameA ClubA | 8.00 8.00 8.50 8.30 8.60 | 3.10 | 27.90 | 7.50 7.70 7.40 7.60 7.50 | 12.30 | 34.90 | 7.50 7.30 7.20 7.50 7.20 | 13.60 | 35.60 | 98.40

I know its hard to read that from what ive written but it doesnt really matter for now.

The database includes many of these records and competitors have many records to their name. What i want to do is query the table to leave only the top 5 'Overall Total' fields from each person. Ive tried a few ways but cannot get anywhere near what i want. If 5 scores for one Name dont exist i wish to leave that person out, however that can be thought about later.

Once that is done i want to enter an average for each person as a new entry into a different table.

any input into what i should be thinking about to do this?

Thanks
Jonny

[chazzy]

i'm a little confused - are you averaging up the values of a single row or are there multiple rows for each name?

[jonnyboy2]

there are multiple rows for each name

for example if competitor 'John Smith' had 6 entries with the 'Total score' for each being

90
91
95
88
85
87.5

the average of all but the lowest 85 would be what i based the ranking on.

thanks in advance for any input

[chazzy]

I personally would use server side logic to handle this, not database.

[bluestartech]

you could use sql but it would be long and messy

[Hen Asraf]

Filter the top 5 ranks with PHP, and go from there using PHP.SELECT `rank` FROM `person` WHERE `person_id` = 'blah' ORDER BY `rank` DESC
And through PHP, do something like this...while ($rank = mysql_fetch_array($result))
{
$avg += $rank['rank'];
}
$avg /= 5;