From: David Garamond <lists@zara.6.isreserved.com>
To: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-sql@postgresql.org
Subject: Re: olympics ranking query
Date: Sat, 21 Aug 2004 00:27:43 +0700
Message-ID: <4126348F.2090604@zara.6.isreserved.com> (raw)
In-Reply-To: <20040820170148.GB26674@wolff.to>
References: <41262968.2000507@zara.6.isreserved.com>
<20040820170148.GB26674@wolff.to>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lists@zara.6.isreserved.com, bruno@wolff.to
Subject: Re: olympics ranking query
In-Reply-To: <4126348F.2090604@zara.6.isreserved.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox