CODEWITHSUNDEEP
sqlpostgresql
Stam
Stam

Reputation: 35

How to add single value in a new column

my goal is to put the value of the 1 row in every row of the new column. First value in this example is the number 10. The New Table is showing my goal.

Table

Product ID     Name       Value  
1             ABC        10 
2             XYZ        22
3             LMM        8

New Table

Product ID     Name       Value  New Column
1             ABC        10         10
2             XYZ        22         10
3             LMM        8          10

I would fetch the value with the row_rumber function, but how i get that value in every row?

Upvotes: 0

Views: 448

Answers (2)

user330315
user330315

Reputation:

You can use the first_value() window function:

select product_id, name, value, 
       first_value(value) over (order by product_id) as new_column
from the_table
order by product_id;

Rows in a table have no implied sort order. So the "first row" can only be defined when an order by is present.

Upvotes: 1

The Impaler
The Impaler

Reputation: 48770

Assuming you want to pick the first one according to the product ID, you can do:

select *,
  ( select value 
    from (select *, row_number() over(order by product_id) as rn from t) x 
    where rn = 1
  ) as new_column
from t

Upvotes: 1

Related Questions