Club Scores

[MooGuru]

Hi,

I am setting up a database for a competition. I have scores for each individual, and the club score is the top four scores from each club. I have two tables, one with the individuals and their scores, and one with the clubs and a field to hold the club scores.

So far, I have managed to do a query that returns the top 4 scores for each club, but only through entering the name of the club each time.

What I really need is this to be automated, such that it will give me the top four scores for each club without me having to manually input each club name (could get tedious). Additionally, I then want to sum these top four scores in each club to store in a table.

Help please!!! Thanks in advance

 

[r937]

something like this --

[tt]select clubname
, sum(individuals.score) as top4scores
from clubs
inner
join individuals XX
on clubs.id = individuals.clubid
where individuals.score
in (select top 4 score
from individuals
where clubid = XX.clubid)
group
by clubname[/tt]

rudy

 

[MooGuru]

Thanks, that worked a treat!!