Select first match based on column rule

user_1651660 0
Viewed 45

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.

enter image description here

snowflake-cloud-data-platform sql
2 Answers

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:

enter image description here

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:

enter image description here

Related: Equivalent for Keep in Snowflake

user_50708790

Maybe I am missing something but if you just need to filter your data for qualifying hiers, isn't this simpler?

select *
from t
where hier in (select min(hier) from t where rule='YES' )
user_99471590
Related