How to add single value in a new column-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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