Select rows with same id and set same value in a row-CodePudding

I tried for hours and read many posts but I still can't figure out how to handle this request:

I have a table like this:

 ------ ------ ------ 
|ID    |NAME  |REKEY |
 ------ ------ ------ 
|1     |NULL  |AB1C  |
 ------ ------ ------ 
|2     |POP   |AB1C  |
 ------ ------ ------ 
|3     |NULL  |DE97  |
 ------ ------ ------ 
|4     |ANNA  |DE97  |
 ------ ------ ------

When I select, the result is like the table above.

I want to get the value 'POP' from the bottom row and replace it on the top row. Only use select because I am not allowed to update the record.

The result is like below:

 ------ ------ ------ 
|ID    |NAME  |REKEY |
 ------ ------ ------ 
|1     |POP   |AB1C  |
 ------ ------ ------ 
|2     |POP   |AB1C  |
 ------ ------ ------ 
|3     |ANNA  |DE97  |
 ------ ------ ------ 
|4     |ANNA  |DE97  |
 ------ ------ ------

CodePudding user response：

Maybe try using max() :

select ID,
max(NAME) over(partition by REKEY) as LIEFNR,
REKEY
from table_name;

CodePudding user response：

Try this, Table1 is the table with missing values, Also I assumed your REKEY values are same in rows with id 3 & 4 (Let me know if thats not the case). Below will produce the result what you are looking for.

select * from ( select A.ID, B.LIEFNR, A.REKEY from ( select * from Table1 A where LIEFNR is null )A left join ( select * from Table1 B where LIEFNR is not null )B on A.REKEY=B.REKEY )K union

select * from Table1 B where LIEFNR is not null