How to assign a constant to a value on all rows, if that value is adjacent to some value on any row

I have data like this (STATUS, and NAME are the column headers):

STATUS      NAME
ACTIVE      A1
INACTIVE    A1
INACTIVE    B1
INACTIVE    B1

I want to include a CUSTOM field that returns a constant for a particular NAME value, if that NAME value is adjacent to a specific STATUS value on any row in the data:

CUSTOM      STATUS      NAME
ACTIVE      INACTIVE    A1
ACTIVE      ACTIVE      A1
INACTIVE    INACTIVE    B1
INACTIVE    INACTIVE    B1

Because NAME = A1 had a STATUS of ACTIVE in row 2 above, I want CUSTOM to show ACTIVE for all rows tied to the NAME = A1. I tried this for the CUSTOM field:

case when NAME in (select NAME from SOURCE where STATUS='ACTIVE') then 'ACTIVE' else 'INACTIVE' end CUSTOM

But the above is still reflecting the same as STATUS on any given row.

Reading the explanation in the question, it seems that you don't really care about the ordering but you want to know if for the same name there is at least one row with status = 'ACTIVE'.

This can be done with a window function, using OVER (PARTITION BY name). The CASE expression has a default of NULL so MAX() will return null if there is no such value. The final COALESCE() will convert those nulls to 'INACTIVE':

SELECT 
    status, name,
    COALESCE(MAX(CASE WHEN status = 'ACTIVE' THEN status END) 
               OVER (PARTITION BY name), 
             'INACTIVE') AS custom
FROM
    table_name ;

StackExchange Database Administrators Q#139429, answer score: 3