From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Garamond <lists@zara.6.isreserved.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: olympics ranking query
Date: Fri, 20 Aug 2004 13:21:08 -0400
Message-ID: <28621.1093022468@sss.pgh.pa.us> (raw)
In-Reply-To: <412630C8.5000004@zara.6.isreserved.com>
References: <41262968.2000507@zara.6.isreserved.com>
<22979.1093020382@sss.pgh.pa.us>
<412630C8.5000004@zara.6.isreserved.com>
David Garamond <lists@zara.6.isreserved.com> writes:
> This is not quite the same. The ranks are sequential, but they skip, so
> as to match the number of participating countries.
Oh, I missed that bit.
What you really want here is a "running sum" function, that is
SELECT running_sum(numranker) as rank, * FROM
(same subselect as before) ss;
There is no such thing in standard SQL, because it's fundamentally
dependent on the assumption of the input data coming in a particular
order, which is Evil Incarnate according to the relational worldview.
But it's not too hard to do in PLs that allow persistent state.
I recall Elein having exhibited one in plpython(?) not too long ago
--- you might find it on techdocs or varlena.com.
You could brute-force it with a subselect (essentially "SUM(numranker)
over all rows that should precede this one") but that would involve
recomputing the whole subselect for each output row, which doesn't seem
very attractive.
regards, tom lane
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: tgl@sss.pgh.pa.us, lists@zara.6.isreserved.com
Subject: Re: olympics ranking query
In-Reply-To: <28621.1093022468@sss.pgh.pa.us>
* 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