pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsumming tables
13+ messages / 7 participants
[nested] [flat]
* summing tables
@ 2003-07-15 12:09 Erik Thiele <erik@thiele-hydraulik.de>
2003-07-15 12:16 ` Re: summing tables Viorel Dragomir <bigchief@vio.ro>
2003-07-15 12:17 ` Re: summing tables Dani Oderbolz <oderbolz@ecologic.de>
2003-07-15 15:02 ` Re: summing tables Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 3 replies; 13+ messages in thread
From: Erik Thiele @ 2003-07-15 12:09 UTC (permalink / raw)
To: pgsql-sql
hi,
i have a table consisting of 4 integers.
seq is for making the table ordered. (ORDER BY SEQ ASC)
a,b,c maybe null
seq | a | b | c
-----+----+----+---
0 | 1 | 2 | 3
1 | 1 | 2 |
2 | 5 | 7 |
3 | -2 | -4 |
i am needing a sql statement to do
c=a+b+"the c of the row with seq one less than myself"
this statement has to run over the whole table, in seq order.
how can this be acomplished???
cu&thanks
erik
--
Erik Thiele
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: summing tables
2003-07-15 12:09 summing tables Erik Thiele <erik@thiele-hydraulik.de>
@ 2003-07-15 12:16 ` Viorel Dragomir <bigchief@vio.ro>
2003-07-15 12:39 ` Re: summing tables Erik Thiele <erik@thiele-hydraulik.de>
2 siblings, 1 reply; 13+ messages in thread
From: Viorel Dragomir @ 2003-07-15 12:16 UTC (permalink / raw)
To: pgsql-sql
update table_name
set c = a + b + (select c from table_name as x where x.seq = seq-1)
where c is null;
additional checks are required if you want to update c when c is not null
if all the c are null then this query will do nothing
god help u
:)
----- Original Message -----
From: "Erik Thiele" <erik@thiele-hydraulik.de>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, July 15, 2003 3:09 PM
Subject: [SQL] summing tables
> hi,
>
> i have a table consisting of 4 integers.
>
> seq is for making the table ordered. (ORDER BY SEQ ASC)
> a,b,c maybe null
>
>
> seq | a | b | c
> -----+----+----+---
> 0 | 1 | 2 | 3
> 1 | 1 | 2 |
> 2 | 5 | 7 |
> 3 | -2 | -4 |
>
>
> i am needing a sql statement to do
>
> c=a+b+"the c of the row with seq one less than myself"
>
> this statement has to run over the whole table, in seq order.
>
>
> how can this be acomplished???
>
>
> cu&thanks
> erik
>
>
> --
> Erik Thiele
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: summing tables
2003-07-15 12:09 summing tables Erik Thiele <erik@thiele-hydraulik.de>
2003-07-15 12:16 ` Re: summing tables Viorel Dragomir <bigchief@vio.ro>
@ 2003-07-15 12:39 ` Erik Thiele <erik@thiele-hydraulik.de>
2003-07-15 13:37 ` Re: summing tables Viorel Dragomir <bigchief@vio.ro>
2003-07-15 15:14 ` Re: summing tables Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 13+ messages in thread
From: Erik Thiele @ 2003-07-15 12:39 UTC (permalink / raw)
To: pgsql-sql
On Tue, 15 Jul 2003 15:16:21 +0300
"Viorel Dragomir" <bigchief@vio.ro> wrote:
> update table_name
> set c = a + b + (select c from table_name as x where x.seq = seq-1)
> where c is null;
hmmm. the query is run row by row, isn't it?
but it will have different results depending on the order of those rows.
look, the c value is set by one row-query
and read by the row-query of the row below.
does sql specify some "order is magically always as you expect it" rule?
still i am a little confused.
and i am sorry, i didn't initially specify that the "seq" are not gapless.
i.e. seq-1 does not always exist. but seq-13 could be the next lower one!
zeit=# select * from foo;
seq | a | b | c
-----+----+----+---
0 | 1 | 2 | 3
1 | 1 | 2 |
2 | 5 | 7 |
3 | -2 | -4 |
6 | -1 | -2 |
5 | -2 | -2 |
4 | 0 | 1 |
(7 rows)
i created this (gapless for easiness) table and run your query:
zeit=# update foo set c = a + b + (select c from foo as x where x.seq = seq-1) where c is null;
UPDATE 6
#### 6 updates??? really???
zeit=# select * from foo;
seq | a | b | c
-----+----+----+---
0 | 1 | 2 | 3
1 | 1 | 2 |
2 | 5 | 7 |
3 | -2 | -4 |
6 | -1 | -2 |
5 | -2 | -2 |
4 | 0 | 1 |
(7 rows)
hmmmm. let's try the statement of the other reply to my initial mail:
UPDATE foo t set c = a + b + (Select c from foo tt where tt.seq = t.seq -1)
zeit=# UPDATE foo t set c = a + b + (Select c from foo tt where tt.seq = t.seq -1);
ERROR: parser: parse error at or near "t"
hmmmm....
any clues?
cu & thx
erik
> additional checks are required if you want to update c when c is not null
> if all the c are null then this query will do nothing
> > i have a table consisting of 4 integers.
> >
> > seq is for making the table ordered. (ORDER BY SEQ ASC)
> > a,b,c maybe null
> >
> >
> > seq | a | b | c
> > -----+----+----+---
> > 0 | 1 | 2 | 3
> > 1 | 1 | 2 |
> > 2 | 5 | 7 |
> > 3 | -2 | -4 |
> >
> >
> > i am needing a sql statement to do
> >
> > c=a+b+"the c of the row with seq one less than myself"
> >
> > this statement has to run over the whole table, in seq order.
--
Erik Thiele
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: summing tables
2003-07-15 12:09 summing tables Erik Thiele <erik@thiele-hydraulik.de>
2003-07-15 12:16 ` Re: summing tables Viorel Dragomir <bigchief@vio.ro>
2003-07-15 12:39 ` Re: summing tables Erik Thiele <erik@thiele-hydraulik.de>
@ 2003-07-15 13:37 ` Viorel Dragomir <bigchief@vio.ro>
2003-07-15 14:55 ` Re: summing tables Greg Stark <gsstark@mit.edu>
1 sibling, 1 reply; 13+ messages in thread
From: Viorel Dragomir @ 2003-07-15 13:37 UTC (permalink / raw)
To: pgsql-sql
Ok. I disregarded the complexity of this problem. :)
You may wish to do this thing from a programming language with every row at
a time [in php, asp...].
Anyway here is a function in plpgsql.
It solves your problem, i hope; but i don't recommend it.
create function update_nulls() returns int
as '
declare
var1 integer;
var2 integer;
begin
select into var1 count(*) from tab where c is null;
var2 := var1;
while var1 > 0 loop
update table_name
set c = a + b + (select x.c from table_name as x where x.seq =
table_name.seq-1)
where c is null;
var1 := var1 - 1;
end loop;
return var2;
end; '
language 'plpgsql';
----- Original Message -----
From: "Erik Thiele" <erik@thiele-hydraulik.de>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, July 15, 2003 3:39 PM
Subject: Re: [SQL] summing tables
> On Tue, 15 Jul 2003 15:16:21 +0300
> "Viorel Dragomir" <bigchief@vio.ro> wrote:
>
> > update table_name
> > set c = a + b + (select c from table_name as x where x.seq = seq-1)
> > where c is null;
>
> hmmm. the query is run row by row, isn't it?
> but it will have different results depending on the order of those rows.
>
> look, the c value is set by one row-query
> and read by the row-query of the row below.
>
> does sql specify some "order is magically always as you expect it" rule?
>
> still i am a little confused.
>
>
> and i am sorry, i didn't initially specify that the "seq" are not gapless.
> i.e. seq-1 does not always exist. but seq-13 could be the next lower one!
>
> zeit=# select * from foo;
> seq | a | b | c
> -----+----+----+---
> 0 | 1 | 2 | 3
> 1 | 1 | 2 |
> 2 | 5 | 7 |
> 3 | -2 | -4 |
> 6 | -1 | -2 |
> 5 | -2 | -2 |
> 4 | 0 | 1 |
> (7 rows)
>
> i created this (gapless for easiness) table and run your query:
>
> zeit=# update foo set c = a + b + (select c from foo as x where x.seq =
seq-1) where c is null;
> UPDATE 6
>
> #### 6 updates??? really???
>
> zeit=# select * from foo;
> seq | a | b | c
> -----+----+----+---
> 0 | 1 | 2 | 3
> 1 | 1 | 2 |
> 2 | 5 | 7 |
> 3 | -2 | -4 |
> 6 | -1 | -2 |
> 5 | -2 | -2 |
> 4 | 0 | 1 |
> (7 rows)
>
>
> hmmmm. let's try the statement of the other reply to my initial mail:
>
> UPDATE foo t set c = a + b + (Select c from foo tt where tt.seq =
t.seq -1)
>
> zeit=# UPDATE foo t set c = a + b + (Select c from foo tt where tt.seq =
t.seq -1);
> ERROR: parser: parse error at or near "t"
>
> hmmmm....
>
> any clues?
>
> cu & thx
> erik
>
> > additional checks are required if you want to update c when c is not
null
> > if all the c are null then this query will do nothing
> > > i have a table consisting of 4 integers.
> > >
> > > seq is for making the table ordered. (ORDER BY SEQ ASC)
> > > a,b,c maybe null
> > >
> > >
> > > seq | a | b | c
> > > -----+----+----+---
> > > 0 | 1 | 2 | 3
> > > 1 | 1 | 2 |
> > > 2 | 5 | 7 |
> > > 3 | -2 | -4 |
> > >
> > >
> > > i am needing a sql statement to do
> > >
> > > c=a+b+"the c of the row with seq one less than myself"
> > >
> > > this statement has to run over the whole table, in seq order.
>
> --
> Erik Thiele
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: summing tables
2003-07-15 12:09 summing tables Erik Thiele <erik@thiele-hydraulik.de>
2003-07-15 12:16 ` Re: summing tables Viorel Dragomir <bigchief@vio.ro>
2003-07-15 12:39 ` Re: summing tables Erik Thiele <erik@thiele-hydraulik.de>
2003-07-15 13:37 ` Re: summing tables Viorel Dragomir <bigchief@vio.ro>
@ 2003-07-15 14:55 ` Greg Stark <gsstark@mit.edu>
2003-07-15 15:06 ` Re: summing tables Viorel Dragomir <bc@vio.ro>
0 siblings, 1 reply; 13+ messages in thread
From: Greg Stark @ 2003-07-15 14:55 UTC (permalink / raw)
To: Viorel Dragomir <bigchief@vio.ro>; +Cc: pgsql-sql
To solve this problem efficiently you probably need the lead/lag analytic
functions. Unfortunately Postgres doesn't have them.
You could do it with something like:
update foo set c = a+b+(select c from foo as x where seq < foo.seq ORDER BY seq desc LIMIT 1)
or the more standard but likely to be way slower:
update foo set c = a+b+(select c from foo as x where seq = (select max(seq) from foo as y where seq < foo.seq))
However, i would suggest that if you have an implicit relationship between
records you should make that relationship explicit with a foreign key. If you
had a column that contained the seq of the parent record then this would be
easy. I'm really puzzled how this query as currently specified could be
useful.
--
greg
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: summing tables
2003-07-15 12:09 summing tables Erik Thiele <erik@thiele-hydraulik.de>
2003-07-15 12:16 ` Re: summing tables Viorel Dragomir <bigchief@vio.ro>
2003-07-15 12:39 ` Re: summing tables Erik Thiele <erik@thiele-hydraulik.de>
2003-07-15 13:37 ` Re: summing tables Viorel Dragomir <bigchief@vio.ro>
2003-07-15 14:55 ` Re: summing tables Greg Stark <gsstark@mit.edu>
@ 2003-07-15 15:06 ` Viorel Dragomir <bc@vio.ro>
2003-07-15 15:38 ` Re: summing tables Greg Stark <gsstark@mit.edu>
0 siblings, 1 reply; 13+ messages in thread
From: Viorel Dragomir @ 2003-07-15 15:06 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: pgsql-sql
The primary problem was that the update command doesn't modify rows in the
order u want to do it.
I think the update starts with the latest inserted rows. I guess.
Anyway, in real life this update modifies only one row with a value wich is
diff of null.
It was really handy if it was specified the option ORDER for the update
command.
----- Original Message -----
From: "Greg Stark" <gsstark@mit.edu>
To: "Viorel Dragomir" <bigchief@vio.ro>
Cc: <pgsql-sql@postgresql.org>
Sent: Tuesday, July 15, 2003 5:55 PM
Subject: Re: [SQL] summing tables
>
> To solve this problem efficiently you probably need the lead/lag analytic
> functions. Unfortunately Postgres doesn't have them.
>
> You could do it with something like:
>
> update foo set c = a+b+(select c from foo as x where seq < foo.seq ORDER
BY seq desc LIMIT 1)
>
> or the more standard but likely to be way slower:
>
> update foo set c = a+b+(select c from foo as x where seq = (select
max(seq) from foo as y where seq < foo.seq))
>
>
> However, i would suggest that if you have an implicit relationship between
> records you should make that relationship explicit with a foreign key. If
you
> had a column that contained the seq of the parent record then this would
be
> easy. I'm really puzzled how this query as currently specified could be
> useful.
>
>
> --
> greg
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: summing tables
2003-07-15 12:09 summing tables Erik Thiele <erik@thiele-hydraulik.de>
2003-07-15 12:16 ` Re: summing tables Viorel Dragomir <bigchief@vio.ro>
2003-07-15 12:39 ` Re: summing tables Erik Thiele <erik@thiele-hydraulik.de>
2003-07-15 13:37 ` Re: summing tables Viorel Dragomir <bigchief@vio.ro>
2003-07-15 14:55 ` Re: summing tables Greg Stark <gsstark@mit.edu>
2003-07-15 15:06 ` Re: summing tables Viorel Dragomir <bc@vio.ro>
@ 2003-07-15 15:38 ` Greg Stark <gsstark@mit.edu>
0 siblings, 0 replies; 13+ messages in thread
From: Greg Stark @ 2003-07-15 15:38 UTC (permalink / raw)
To: Viorel Dragomir <bc@vio.ro>; +Cc: Greg Stark <gsstark@mit.edu>; pgsql-sql
"Viorel Dragomir" <bc@vio.ro> writes:
> Anyway, in real life this update modifies only one row with a value wich is
> diff of null. It was really handy if it was specified the option ORDER for
> the update command.
Are you hoping to produce a running total? That's very difficult in standard
SQL. That would be very different from the query you asked for.
Running totals, ranking, lead/lag, are all things that are very difficult to
do in standard SQL. They don't fit in the unordered set model that SQL follows
so doing them without special non-standard functions is very hard and
inefficient.
The functions to do them don't fit well within the SQL universe either, which
might be why they don't exist yet in postgres.
--
greg
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: summing tables
2003-07-15 12:09 summing tables Erik Thiele <erik@thiele-hydraulik.de>
2003-07-15 12:16 ` Re: summing tables Viorel Dragomir <bigchief@vio.ro>
2003-07-15 12:39 ` Re: summing tables Erik Thiele <erik@thiele-hydraulik.de>
@ 2003-07-15 15:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-15 15:32 ` Re: summing tables Viorel Dragomir <bigchief@vio.ro>
1 sibling, 1 reply; 13+ messages in thread
From: Tom Lane @ 2003-07-15 15:14 UTC (permalink / raw)
To: Erik Thiele <erik@thiele-hydraulik.de>; +Cc: pgsql-sql
Erik Thiele <erik@thiele-hydraulik.de> writes:
> "Viorel Dragomir" <bigchief@vio.ro> wrote:
>> update table_name
>> set c = a + b + (select c from table_name as x where x.seq = seq-1)
>> where c is null;
> hmmm. the query is run row by row, isn't it?
> but it will have different results depending on the order of those rows.
No, it won't, because the SELECTs will not see the changes from the
not-yet-completed UPDATE. The above command is almost right; it needs
to be
update table_name
set c = a + b + (select c from table_name as x where seq = table_name.seq-1)
where c is null;
because inside the sub-SELECT, unadorned "seq" will refer to the SELECT's
table.
You didn't say exactly what you wanted to do with null inputs, so that
issue may need more thought.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: summing tables
2003-07-15 12:09 summing tables Erik Thiele <erik@thiele-hydraulik.de>
2003-07-15 12:16 ` Re: summing tables Viorel Dragomir <bigchief@vio.ro>
2003-07-15 12:39 ` Re: summing tables Erik Thiele <erik@thiele-hydraulik.de>
2003-07-15 15:14 ` Re: summing tables Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-07-15 15:32 ` Viorel Dragomir <bigchief@vio.ro>
2003-07-15 15:42 ` Re: summing tables Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Viorel Dragomir @ 2003-07-15 15:32 UTC (permalink / raw)
To: pgsql-sql
Indeed it was a mistake not to put the table_name. in where clause.
But this doesn't resolve the problem.
Do you know in which order the update will modify the rows?
My lucky guess is that it takes from last inserted rows to the first row. In
this way only one row gets updated correctly.
I add the result from my unfortunate solution, even corrected.
select * from table_name;
seq | a | b | c
-----+---+---+---
1 | 1 | 2 | 3
2 | 5 | 9 |
3 | 1 | 2 |
4 | 4 | 7 |
5 | 4 | 2 |
6 | 0 | 1 |
(6 rows)
update table_name
set c = a + b + (select c from table_name as x where seq =
table_name.seq-1)
where c is null;
select * from table_name;
seq | a | b | c
-----+---+---+----
1 | 1 | 2 | 3
2 | 5 | 9 | 17
3 | 1 | 2 |
4 | 4 | 7 |
5 | 4 | 2 |
6 | 0 | 1 |
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Erik Thiele" <erik@thiele-hydraulik.de>
Cc: <pgsql-sql@postgresql.org>
Sent: Tuesday, July 15, 2003 6:14 PM
Subject: Re: [SQL] summing tables
> Erik Thiele <erik@thiele-hydraulik.de> writes:
> > "Viorel Dragomir" <bigchief@vio.ro> wrote:
> >> update table_name
> >> set c = a + b + (select c from table_name as x where x.seq = seq-1)
> >> where c is null;
>
> > hmmm. the query is run row by row, isn't it?
> > but it will have different results depending on the order of those rows.
>
> No, it won't, because the SELECTs will not see the changes from the
> not-yet-completed UPDATE. The above command is almost right; it needs
> to be
>
> update table_name
> set c = a + b + (select c from table_name as x where seq =
table_name.seq-1)
> where c is null;
>
> because inside the sub-SELECT, unadorned "seq" will refer to the SELECT's
> table.
>
> You didn't say exactly what you wanted to do with null inputs, so that
> issue may need more thought.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: summing tables
2003-07-15 12:09 summing tables Erik Thiele <erik@thiele-hydraulik.de>
2003-07-15 12:16 ` Re: summing tables Viorel Dragomir <bigchief@vio.ro>
2003-07-15 12:39 ` Re: summing tables Erik Thiele <erik@thiele-hydraulik.de>
2003-07-15 15:14 ` Re: summing tables Tom Lane <tgl@sss.pgh.pa.us>
2003-07-15 15:32 ` Re: summing tables Viorel Dragomir <bigchief@vio.ro>
@ 2003-07-15 15:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-15 15:49 ` Re: summing tables Viorel Dragomir <bigchief@vio.ro>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2003-07-15 15:42 UTC (permalink / raw)
To: Viorel Dragomir <bigchief@vio.ro>; +Cc: pgsql-sql
"Viorel Dragomir" <bigchief@vio.ro> writes:
> Indeed it was a mistake not to put the table_name. in where clause.
> But this doesn't resolve the problem.
> Do you know in which order the update will modify the rows?
No, and *it does not matter*. You are forgetting that this all runs
under MVCC rules. The sub-SELECTs will see the pre-existing versions
of the rows, whether or not the UPDATE has yet produced new versions.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: summing tables
2003-07-15 12:09 summing tables Erik Thiele <erik@thiele-hydraulik.de>
2003-07-15 12:16 ` Re: summing tables Viorel Dragomir <bigchief@vio.ro>
2003-07-15 12:39 ` Re: summing tables Erik Thiele <erik@thiele-hydraulik.de>
2003-07-15 15:14 ` Re: summing tables Tom Lane <tgl@sss.pgh.pa.us>
2003-07-15 15:32 ` Re: summing tables Viorel Dragomir <bigchief@vio.ro>
2003-07-15 15:42 ` Re: summing tables Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-07-15 15:49 ` Viorel Dragomir <bigchief@vio.ro>
0 siblings, 0 replies; 13+ messages in thread
From: Viorel Dragomir @ 2003-07-15 15:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Viorel Dragomir" <bigchief@vio.ro>
Cc: <pgsql-sql@postgresql.org>
Sent: Tuesday, July 15, 2003 6:42 PM
Subject: Re: [SQL] summing tables
> "Viorel Dragomir" <bigchief@vio.ro> writes:
> > Indeed it was a mistake not to put the table_name. in where clause.
> > But this doesn't resolve the problem.
>
> > Do you know in which order the update will modify the rows?
>
> No, and *it does not matter*. You are forgetting that this all runs
> under MVCC rules. The sub-SELECTs will see the pre-existing versions
> of the rows, whether or not the UPDATE has yet produced new versions.
Yes that was correct.
I forgot that the subselect sees only the old rows.
And the ORDER option will not make any difference, only making fool out of
myself. :)
I don't know if the problem is solved with my posted function.
Anyway this is the mail that started all [i get a lot of emails and i'm not
really searching for a solution on this matter]:
"
hi,
i have a table consisting of 4 integers.
seq is for making the table ordered. (ORDER BY SEQ ASC)
a,b,c maybe null
seq | a | b | c
-----+----+----+---
0 | 1 | 2 | 3
1 | 1 | 2 |
2 | 5 | 7 |
3 | -2 | -4 |
i am needing a sql statement to do
c=a+b+"the c of the row with seq one less than myself"
this statement has to run over the whole table, in seq order.
how can this be acomplished???
cu&thanks
erik
--
Erik Thiele
"
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: summing tables
2003-07-15 12:09 summing tables Erik Thiele <erik@thiele-hydraulik.de>
@ 2003-07-15 12:17 ` Dani Oderbolz <oderbolz@ecologic.de>
2 siblings, 0 replies; 13+ messages in thread
From: Dani Oderbolz @ 2003-07-15 12:17 UTC (permalink / raw)
To: Erik Thiele <erik@thiele-hydraulik.de>; pgsql-sql
Erik Thiele wrote:
>hi,
>
>i have a table consisting of 4 integers.
>
>seq is for making the table ordered. (ORDER BY SEQ ASC)
>a,b,c maybe null
>
>
> seq | a | b | c
>-----+----+----+---
> 0 | 1 | 2 | 3
> 1 | 1 | 2 |
> 2 | 5 | 7 |
> 3 | -2 | -4 |
>
>
>i am needing a sql statement to do
>
>c=a+b+"the c of the row with seq one less than myself"
>
>this statement has to run over the whole table, in seq order.
>
>
>how can this be acomplished???
>
>
>cu&thanks
>erik
>
>
>
>
Hi Erik,
this should do the trick:
(I have not yet considered the NULLS though...)
UPDATE table t set c = a + b + (Select c from table tt where tt.seq =
t.seq -1)
Cheers, Dani
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: summing tables
2003-07-15 12:09 summing tables Erik Thiele <erik@thiele-hydraulik.de>
@ 2003-07-15 15:02 ` Jean-Luc Lachance <jllachan@nsd.ca>
2 siblings, 0 replies; 13+ messages in thread
From: Jean-Luc Lachance @ 2003-07-15 15:02 UTC (permalink / raw)
To: Erik Thiele <erik@thiele-hydraulik.de>; +Cc: pgsql-sql
Erik,
If you intent is to get a running total of a and b ordered by seq, you
should try this (assuming the table name is t):
update t set c = ( select sum(a) + sum(b) from t t1 where t1.seq <=
t.seq);
You should have an index on seq.
If the table is very large, it is going to be painfully slow.
In that case you may want to think about using a function to step thru
each row.
JLL
Erik Thiele wrote:
>
> hi,
>
> i have a table consisting of 4 integers.
>
> seq is for making the table ordered. (ORDER BY SEQ ASC)
> a,b,c maybe null
>
> seq | a | b | c
> -----+----+----+---
> 0 | 1 | 2 | 3
> 1 | 1 | 2 |
> 2 | 5 | 7 |
> 3 | -2 | -4 |
>
> i am needing a sql statement to do
>
> c=a+b+"the c of the row with seq one less than myself"
>
> this statement has to run over the whole table, in seq order.
>
> how can this be acomplished???
>
> cu&thanks
> erik
>
> --
> Erik Thiele
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2003-07-15 15:49 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-15 12:09 summing tables Erik Thiele <erik@thiele-hydraulik.de>
2003-07-15 12:16 ` Viorel Dragomir <bigchief@vio.ro>
2003-07-15 12:39 ` Erik Thiele <erik@thiele-hydraulik.de>
2003-07-15 13:37 ` Viorel Dragomir <bigchief@vio.ro>
2003-07-15 14:55 ` Greg Stark <gsstark@mit.edu>
2003-07-15 15:06 ` Viorel Dragomir <bc@vio.ro>
2003-07-15 15:38 ` Greg Stark <gsstark@mit.edu>
2003-07-15 15:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-15 15:32 ` Viorel Dragomir <bigchief@vio.ro>
2003-07-15 15:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-15 15:49 ` Viorel Dragomir <bigchief@vio.ro>
2003-07-15 12:17 ` Dani Oderbolz <oderbolz@ecologic.de>
2003-07-15 15:02 ` Jean-Luc Lachance <jllachan@nsd.ca>
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