create new column based on rows in sql

Question

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.

Please post data as text not images. What rdbms are you using: mysql, sql server etc? — user18098820
– user18098820, Commented Mar 17, 2022 at 12:22
i am using mysql. I have tried to post data as text but it becomes very messy. that's why post it as image. — Saddaf Afrin Khan
– Saddaf Afrin Khan, Commented Mar 17, 2022 at 12:25

marcothesane · Accepted Answer · 2022-03-17 12:33:34Z

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

I assume your DBMS supports the LAST_VALUE(... IGNORE NULLS) OLAP function
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)

Collectives™ on Stack Overflow

create new column based on rows in sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related