Posted by strawberry on 06/26/06 10:04

Read Bill Karwin's answer to a similar problem at

http://groups.google.com/group/comp.databases.mysql/browse_thread/thread/ca7a0470999833fd?hl=en

Roland Zitzke wrote:
> 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]