Posted by Roland Zitzke on 07/13/06 05:38

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

[Back to original message]