Re: olympics ranking query

Bruno Wolff III wrote:
> On Fri, Aug 20, 2004 at 23:40:08 +0700,
> David Garamond <lists(at)zara(dot)6(dot)isreserved(dot)com> wrote:
>
>>Challenge question: is there a simpler way to do query #1 (without any
>>PL, and if possible without sequences too?
>
> You could use a subselect to count how many countries had a lower
> medal ranking and add 1 to get the rank. This should be a lot more standard
> than using sequences. It will probably be a little slower, but for tables
> of that size it shouldn't be a big deal.

Thanks for the tip. This is what I came up with:

select
(select count(*) from countrymedal c1 where
c1.gold>c2.gold or
(c1.gold=c2.gold and (c1.silver>c2.silver or
(c1.silver=c2.silver and c1.bronze>c2.bronze))))+1 as rank,
count(*) as numranker,
gold, silver, bronze
from countrymedal c2
group by gold, silver, bronze
order by rank;

--
dave