SQL - Subselect in select clause - how to create column which decides uniqity logic

Question

I am trying to write subselect which will run through returned data, then checks status of all and then decides uniquity logic.

Is there any way to find out following ?

• case any of data has 'Active' status first one will be marked as 1 everything else as 0
• case there is no 'Active' status then first 'Expired' status will by marked as 1 and everything else as 0
• case there is no 'Active' and 'Expired' status then first 'In Progress' will be marked as 1 and everything else as 0

I was trying to write it like this but i need to have it in one case statement

SELECT a.id, a.status,
,(SELECT 
    CASE WHEN b.STATUS = 'Active' THEN 1 ELSE 0 END
    CASE WHEN b.STATUS = 'Expired' THEN 1 ELSE 0 END 
    FROM b.TABLE
    WHERE a.id=b.id )AS unique
FROM my.TABLE 

Result should look like https://i.sstatic.net/qCA74.png picture for expired case

Thank you in advance for any tips.

Answer 1

Use a window function:

select t.*,
       (case when row_number() over (partition by id
                                     order by case status when 'Active' then 1 when 'Expired' then 2 else 3 end
                                    ) = 1
             then 1 else 0
        end) as unique_flag
from my.table t;