Posted by strawberry on 07/01/06 15:49

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" <zac.carey@gmail.com> 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

[Back to original message]