Posted by Roland Zitzke on 06/26/06 08:46

Hi,
I have two tables (simplified)
families {
lastName : char[32]
}
People
lastName : char[32]
score : integer
}
Multiple people can belong to one family where lastName is the common key
field
In this setup there could be families with no members and people not found
in "families".
Each person (found in people) can reach a score
My task is to rank families such that they are sorted by the sum of scores
of their best three members.
In other words, the best three members of each family count - if there are
more members they are ignored.
The sum of those three best members makes the score for the family and the
query should output all families (lastName) in ranked order with there score
i.e. the sum of their three best ember scores).
Any easy solutions?

Thanks and regards
Roland

[Back to original message]