Message-ID: <4126348F.2090604@zara.6.isreserved.com>
Date: Sat, 21 Aug 2004 00:27:43 +0700
From: David Garamond <lists@zara.6.isreserved.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7b) Gecko/20040421
MIME-Version: 1.0
To: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-sql@postgresql.org
Subject: Re: olympics ranking query
References: <41262968.2000507@zara.6.isreserved.com>
	<20040820170148.GB26674@wolff.to>
In-Reply-To: <20040820170148.GB26674@wolff.to>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Bruno Wolff III wrote:
> On Fri, Aug 20, 2004 at 23:40:08 +0700,
>   David Garamond <lists@zara.6.isreserved.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