You can use a CASE expression to assign 'Working on It' to all those records that have at least one of that status "MAX(Status='Working') = 1" for each "Item".

SELECT Item,
       CASE WHEN MAX(CASE WHEN Status='Working on It' THEN 1 END) OVER(PARTITION BY Item)=1
            THEN 'Working on It'
            ELSE Status
       END AS Status
FROM tab

If you're using SUBSTRING(item, 1, 4) instead of Item , you also need OVER(PARTITION BY SUBSTRING(item, 1, 4)) .

It is a bad idea to use the same name for an expression as for the original column. You call your expression item , but it is actually just a substring of the original item . This confused you and you partitioned your MAX OVER by item . The DBMS doesn't yet see the alias you created and assumes the column. Here is the corrected query:

SELECT DISTINCT 
    SUBSTRING(item, 1, 4) AS item,
    CASE 
        WHEN MAX(CASE 
                     WHEN [Eng Status] = 'Working on It' 
                         THEN 1 
                 END) OVER (PARTITION BY SUBSTRING(item, 1, 4)) = 1
            THEN 'Working on It'
            ELSE [Eng Status]
    END AS Status
FROM 
    tbl