Several years ago someone posted a question about how to achieve a
running total of columns in sql. I have been trying to find a solution
to a similar problem for some time and just came up with something that
works great for me so I thought I'd post it for the benefit of the list.
The proble
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
Bruno Wolff III wrote:
On Fri, Aug 20, 2004 at 23:40:08 +0700,
David Garamond <[EMAIL PROTECTED]> 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 ranki
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) a
Tom Lane wrote:
Challenge question: is there a simpler way to do query #1 (without any
PL, and if possible without sequences too?
Can't without sequences AFAIK, but you certainly can do it simpler:
select setval('seq1', 0);
select nextval('seq1'), * from
(select count(*) as numranker,
gold, silve
On Fri, Aug 20, 2004 at 23:40:08 +0700,
David Garamond <[EMAIL PROTECTED]> 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
David Garamond <[EMAIL PROTECTED]> writes:
> Challenge question: is there a simpler way to do query #1 (without any
> PL, and if possible without sequences too?
Can't without sequences AFAIK, but you certainly can do it simpler:
select setval('seq1', 0);
select nextval('seq1'), * from
(select c
See http://www.athens2004.com/en/OlympicMedals/medals?noc=MGL .
create table countrymedal (
countryid CHAR(3) PRIMARY KEY,
gold INT NOT NULL,
silver INT NOT NULL,
bronze INT NOT NULL);
COPY countrymedal (countryid, gold, silver, bronze) FROM stdin;
ITA 5 6 3
FRA 5
