How to assign a constant to a value on all rows, if that value is adjacent to some value on any 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 ;