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: (view raw or whole 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 count(*) from countrymedal c1 where> or
      ( 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;


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-2015 The PostgreSQL Global Development Group