Posted by strawberry on 10/14/06 11:51

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-display-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" <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]