CODEWITHSUNDEEP
sqldb2
Robert Fingerhut
Robert Fingerhut

Reputation: 19

Trying to create a flag based on ranking values in two separate columns

I have a query where I have duplicate rows for certain ID's, but have two different columns created by window functions and need to create a flag for each row based on a certain order. I've provided an example below of what the data looks like.

I have a RANK_ONE column that returns a ranked value or null and not every ID has a value, but if it has a one, I need it to return a 1 for the row that contains 1.

The RANK_TWO column is basically the same, but I need to flag the 1 row IF there isn't already a 1 flagged from RANK_ONE for the same ID.

enter image description here

The PRIMARY column is my desired outcome. Any thoughts?

I don't know if I've just been in the query too long and can't see a simple solution right in front of me, but it's driving me nuts trying to figure it out.

Upvotes: 0

Views: 113

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You seem to want rank_one if it is every 1 for the id and otherwise rank_two:

select t.*,
       (case when max(case when rank_one = 1 then 1 else 0 end) over (partition by id) = 1 and rank_one = 1 then 1
             when max(case when rank_one = 1 then 1 else 0 end) over (partition by id) = 1 then 0
             when rank_two = 1 then 1
             else 0
        end) as primary
from t;

Upvotes: 1

Related Questions