That 'running aggregate' notion comes up quite regularily, and it has a number of nonintuitive holes, to do with what happens when your ordering allows for ties. ASTRID had it, RedBrick SQL had it, the latest MSSQL has it ... not necessarily a recommendation.
Tom Lane wrote:
David Garamond <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
