I'd like to apply the value of a column in the Nth row of my table to all rows in the table. Is this possible in PostgreSQL alone?
1
-
What do you mean "apply the value of a column"? Can you provide some example of code you have tried or what the data looks like?Gordon Linoff– Gordon Linoff2013-03-21 20:22:44 +00:00Commented Mar 21, 2013 at 20:22
Add a comment
|
2 Answers
select *
from <<TABLE>>
limit 1 offset <N>
Edit: Sorry, misread your message. I didn't realize you needed an update as well.
UPDATE <<TABLE1>>
SET <<COLUMN1> = (SELECT <<COLUMN2>> FROM <<TABLE2>> limit 1 offset <<N>>)
1 Comment
Erwin Brandstetter
OFFSET <<N-1>> to be precise. And it can be from the same column, too.If you didn't have write access, you could do something with window functions. This could probably be simplified or expanded (particularly with how it grabs 'n', depending on the use case). Unfortunately, you cannot have window functions within window functions.
create temp table test(val numeric);
insert into test select * from generate_series(101,200,1);
select max(case when rn = n then val else null end) over () as nthvalue, val
from(
select row_number() over (order by val) as rn, val, n
from (select val, 9 as n from test) as added_n) as obtained_rownum;
