Posted by Roland Zitzke on 06/26/06 10:32

Thanks,
but this solves only the part of obtaining the first n elements droping the
remaining ones.
It does not really do a ranking based on a sum of values.
BTW:
I simplified my tables to much and dropped an identifyer of people so my
tables would look like:
People
lastName : char[32]
score : integer
memberNumber : integer
}
giving a chance to identify a person.
/Roland

"strawberry" <zac.carey@gmail.com> schrieb im Newsbeitrag
news:1151316274.611316.299420@i40g2000cwc.googlegroups.com...
> 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]