I am having an issue. I need to creat a new column based on row values. so my table is

id | Name   |  status |   
- ------- -------
1  | ANNA   |  M      | 
1  | anna   | null    | 
2  | DIVA   | null    | 
2  | diva   | null    |

based on the status column I want to create a column new_status. where it will check if the id is same and the value of any row of status column is non null then the new column would have that non null value otherwise it will be null. like the image below

id | Name   |  status | new_status
-- ------ ------- ------------
1  | ANNA   |  M      | M
1  | anna   | null    | M
2  | DIVA   | null    | null
2  | diva   | null    | null

please help me with this.

CodePudding user response：

Two comments, as you are not supplying the DBMS you use:

1. I assume your DBMS supports the LAST_VALUE(... IGNORE NULLS) OLAP function
2. I am shamelessly taking advantage of the fact that capital letters come before small letters. Otherwise, you would need a third, sequence column within the id values; SQL tables are not sorted by default, and I would PARTITION BY id ORDER BY sequence) then.

But, otherwise:

\pset null (null)
WITH
indata(id,nam,status) AS (
            SELECT 1,'ANNA','M'
  UNION ALL SELECT 1,'anna',NULL
  UNION ALL SELECT 2,'DIVA',NULL
  UNION ALL SELECT 2,'diva',NULL
)
SELECT
  *
, LAST_VALUE(status IGNORE NULLS) OVER(PARTITION BY id ORDER BY nam) AS new_status
FROM indata
ORDER BY id,nam;

-- out Null display is "(null)".
-- out  id | nam  | status | new_status 
-- out ---- ------ -------- ------------
-- out   1 | ANNA | M      | M
-- out   1 | anna | (null) | M
-- out   2 | DIVA | (null) | (null)
-- out   2 | diva | (null) | (null)