Skip site navigation (1) Skip section navigation (2)

Re: olympics ranking query

From: David Garamond <lists(at)zara(dot)6(dot)isreserved(dot)com>
To: Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: olympics ranking query
Date: 2004-08-20 17:27:43
Message-ID: 4126348F.2090604@zara.6.isreserved.com (view raw or flat)
Thread:
Lists: pgsql-sql
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

In response to

pgsql-sql by date

Next:From: Mischa SandbergDate: 2004-08-20 18:29:38
Subject: Re: olympics ranking query
Previous:From: Tom LaneDate: 2004-08-20 17:21:08
Subject: Re: olympics ranking query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group