SQL Select One Record over another based on column value

What I am trying to do is select rows based off of a ‘priority’.

Say I have this:

ControlID   ProgramID   Priority
1           4           0
1           4           1  
2           4           0 

I want to choose one row each for the control ids (the whole row), which would be the third row, because there is no priority, and the 2nd row becuase it has priority. So if I have two control IDs that are the same, the one I want to choose is the one with ‘priority’.

So my results would be:

ControlID   ProgramID   Priority
1           4           1  
2           4           0 

I’ve tried doing a sub query but I’m not that good at them…

Answer

You can do that by using row_number:

    with r as (
      select
        ControlId,
        ProgramId,
        Priority,
        row_number() over(partition by ControlId order by Priority desc) rn
    )
    select
       ControlId,
       ProgramId,
       Priority
    from r
    where rn = 1