Best solution for ranking problem?

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

Re: Best solution for ranking problem?

Read Bill Karwin's answer to a similar problem at

http://groups.google.com/group/comp.databases.mysql/browse_t hread/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

Re: Best solution for ranking problem?

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" 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_t hread/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
>

Re: Best solution for ranking problem?

OK, well I think this is the answer...

(based on r937's answer to a similar problem at
http://forums.devshed.com/mysql-help-4/group-by-should-displ ay-3-records-from-each-group-how-355842.html)


select lastname,sum(score) from (
select p1.lastname
, p1.firstname
, p1.score
from people as p1
inner
join people as p2
on p1.lastname = p2.lastname
and p1.score <= p2.score
group
by p1.lastname
, p1.score
having count(*) <= 3
order
by p1.lastname
, p1.score desc) xxx
group by lastname order by score desc;

Roland Zitzke wrote:
> 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" 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_t hread/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
> >

Re: Best solution for ranking problem?

"strawberry" schrieb

> select lastname,sum(score) from (
> select p1.lastname
> , p1.firstname
> , p1.score
> from people as p1
> inner
> join people as p2
> on p1.lastname = p2.lastname
> and p1.score <= p2.score
> group
> by p1.lastname
> , p1.score
> having count(*) <= 3
> order
> by p1.lastname
> , p1.score desc) xxx
> group by lastname order by score desc;

Yes, this is exactly what I needed - surely a brain twister.
It might be worth puting a construct like this in some SQL FAQ as many
people probably need to solve simmilar problems of selective ranking.

Thanks
/Roland

Re: Best solution for ranking problem?

I'm not sure what happens when there's a tie between members of the
same family!

Roland Zitzke wrote:
> "strawberry" schrieb
>
> > select lastname,sum(score) from (
> > select p1.lastname
> > , p1.firstname
> > , p1.score
> > from people as p1
> > inner
> > join people as p2
> > on p1.lastname = p2.lastname
> > and p1.score <= p2.score
> > group
> > by p1.lastname
> > , p1.score
> > having count(*) <= 3
> > order
> > by p1.lastname
> > , p1.score desc) xxx
> > group by lastname order by score desc;
>
> Yes, this is exactly what I needed - surely a brain twister.
> It might be worth puting a construct like this in some SQL FAQ as many
> people probably need to solve simmilar problems of selective ranking.
>
> Thanks
> /Roland

Re: Best solution for ranking problem?

OK, this solution deals with ties - one for the cookbooks!

SELECT lastname, sum(score) totals from (
select p1.lastname, p1.firstname, score, rank
from people p1
join
(select count(*) rank,
a.lastname, a.firstname
from people A join
people B
on (A.lastname = B.lastname
and A.score = B.score
and A.firstname <= B.firstname)
or (A.lastname = B.lastname
and A.score < B.score)
group by A.lastname, A.firstname) N
on p1.lastname= N.lastname and
p1.firstname = N.firstname
order by p1.lastname, rank) xxx
where rank <= 3
group by lastname
order by totals desc, lastname;


strawberry wrote:
> I'm not sure what happens when there's a tie between members of the
> same family!
>
> Roland Zitzke wrote:
> > "strawberry" schrieb
> >
> > > select lastname,sum(score) from (
> > > select p1.lastname
> > > , p1.firstname
> > > , p1.score
> > > from people as p1
> > > inner
> > > join people as p2
> > > on p1.lastname = p2.lastname
> > > and p1.score <= p2.score
> > > group
> > > by p1.lastname
> > > , p1.score
> > > having count(*) <= 3
> > > order
> > > by p1.lastname
> > > , p1.score desc) xxx
> > > group by lastname order by score desc;
> >
> > Yes, this is exactly what I needed - surely a brain twister.
> > It might be worth puting a construct like this in some SQL FAQ as many
> > people probably need to solve simmilar problems of selective ranking.
> >
> > Thanks
> > /Roland

Re: Best solution for ranking problem?

"strawberry" schrieb im Newsbeitrag
news:1151410345.401200.258560@c74g2000cwc.googlegroups.com.. .
> I'm not sure what happens when there's a tie between members of the
> same family!
>
There we got a problem. MySQL ignores both members with a tie in this query.
The desired behaviour is to consider a maximum of three members and if the
third and fourth member have the same score (in which case we can't even
decide who is number 3 and who's number 4) only one of them counts.


Roland Zitzke wrote:
>> "strawberry" schrieb
>>
>> > select lastname,sum(score) from (
>> > select p1.lastname
>> > , p1.firstname
>> > , p1.score
>> > from people as p1
>> > inner
>> > join people as p2
>> > on p1.lastname = p2.lastname
>> > and p1.score <= p2.score
>> > group
>> > by p1.lastname
>> > , p1.score
>> > having count(*) <= 3
>> > order
>> > by p1.lastname
>> > , p1.score desc) xxx
>> > group by lastname order by score desc;
>>
>> Yes, this is exactly what I needed - surely a brain twister.
>> It might be worth puting a construct like this in some SQL FAQ as many
>> people probably need to solve simmilar problems of selective ranking.
>>
>> Thanks
>> /Roland
>

Re: Best solution for ranking problem?

Roland Zitzke wrote:
> "strawberry" schrieb im Newsbeitrag
> news:1151410345.401200.258560@c74g2000cwc.googlegroups.com.. .
> > I'm not sure what happens when there's a tie between members of the
> > same family!
> >
> There we got a problem. MySQL ignores both members with a tie in this query.
> The desired behaviour is to consider a maximum of three members and if the
> third and fourth member have the same score (in which case we can't even
> decide who is number 3 and who's number 4) only one of them counts.
>
>
> Roland Zitzke wrote:
> >> "strawberry" schrieb
> >>
> >> > select lastname,sum(score) from (
> >> > select p1.lastname
> >> > , p1.firstname
> >> > , p1.score
> >> > from people as p1
> >> > inner
> >> > join people as p2
> >> > on p1.lastname = p2.lastname
> >> > and p1.score <= p2.score
> >> > group
> >> > by p1.lastname
> >> > , p1.score
> >> > having count(*) <= 3
> >> > order
> >> > by p1.lastname
> >> > , p1.score desc) xxx
> >> > group by lastname order by score desc;
> >>
> >> Yes, this is exactly what I needed - surely a brain twister.
> >> It might be worth puting a construct like this in some SQL FAQ as many
> >> people probably need to solve simmilar problems of selective ranking.
> >>
> >> Thanks
> >> /Roland
> >

hence see my follow-up solution

Re: Best solution for ranking problem?

"strawberry" schrieb im Newsbeitrag

> hence see my follow-up solution
>

Sorry, somehow my newsreader didn't show me or didn't download it - thanks.
/Roland