|
|
Posted by strawberry on 07/13/06 15:00
Roland Zitzke wrote:
> "strawberry" <zac.carey@gmail.com> 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" <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
> >
hence see my follow-up solution
Navigation:
[Reply to this message]
|