Select first match based on column rule

April 20, 2022

Problem Statement: need any first match based on hier rules column defined
Example: like no match for hier 1, but hier 2 is matched, then select sec_id for hier 2

Sample:

with cte_tab as (select column1 as hier, column2 as rule, column3 as sec_id from values
       (1, 'NO','COM'), (1, 'NO','COM'), (2, 'NO','COM'), (3, 'YES','COM')
    order by column1)
select * from cte_tab;

Expected Output:
From the example below, the expected output would be the row with hier = 3 as it’s the first match from the group for which rule=’YES’

Note: there could be multiple entries for the hier and returning all of them is a valid scenario if the rule = ‘YES’ is valid for that group.

>Solution :

Using QAULIFY and DENSE_RANK:

with cte_tab as (
   select column1 as hier, column2 as rule, column3 as sec_id 
   from values
       (1, 'NO','COM'), (1, 'NO','COM'), (2, 'NO','COM'), (3, 'YES','COM')
   order by column1)
select * 
from cte_tab
QUALIFY DENSE_RANK() OVER(ORDER BY RULE='YES' DESC, HIER) = 1;

This version returns only YES for HIER 3

Output:

If the goal is to get entire HIER 3 regardless of rule then:

with cte_tab as (
    select column1 as hier, column2 as rule, column3 as sec_id 
    from values
       (1, 'NO','COM'), (1, 'NO','COM'), (2, 'NO','COM'),
       (3, 'YES','COM'), (3, 'NO', 'A')
    order by column1)
select * 
from cte_tab
QUALIFY HIER=(ARRAY_AGG(HIER) WITHIN GROUP(ORDER BY RULE='YES' DESC) OVER())[0]::TEXT

Output: