Group by query in snowflake-CodePudding

I have a Snowflake table like the following one:

And I wanted to get for each distinct combination "COMPANY"-"BUSINESS UNIT"-"APPROVER LEVEL", the entry with the most recent data (In case we have more than one entry with most recent date it should return all the entries). Thus, considering the table of the example above, it would return the following:

What is the SQL query I have to write in Snowflake order to obtain this?

CodePudding user response：

You could utilize Qualify to handle this. To do so, something like this should work

SELECT * 
  FROM table_a
QUALIFY ROW_NUMBER() OVER (PARTITION BY COMPANY, BUSINESS_UNIT, APPROVER_LEVEL
                  ORDER BY  VALID_FROM DESC) = 1
;

CodePudding user response：

The main idea is same as suggested by @Brandon Coleman, just need to tweek a bit.

with cte as
(
select max(rn) max_rn from (
select rank() over (partition by company,bu,appr_lvl 
order by valid_from) rn 
from your_table_name)
)
select *,rank() over (partition by company,bu,appr_lvl 
order by valid_from) rn 
from your_table_name,cte 
qualify rn>=cte.max_rn;