Finding Latest First x among consecutive x from table-CodePudding

I am trying to write a query to find first latest 1's from each group as below. For example, for Group 1, It shouldn't be 1/2/2022 since it has 1/6/2022 which was shown later. Shouldn't be 1/7/2022 too for Group 1.

Please let me know if you have any idea.

Thanks!

Table x (AsOfDate, Group_Id, Value)
AsOfDate   Group_Id   Value  
1/1/2022   1          0
1/1/2022   2          1
1/2/2022   1          1
1/2/2022   2          1
1/3/2022   1          0
1/3/2022   2          0
1/4/2022   1          0
1/4/2022   2          0
1/5/2022   1          0
1/5/2022   2          1
1/6/2022   1          1
1/6/2022   2          0
1/7/2022   1          1
1/7/2022   2          0

Output
AsOfDate   Group_Id
1/6/2022   1   
1/5/2022   2

CodePudding user response：

What you want is find the earliest date of the last group for continuous row with Value = 1

Use LAG() window function to find the continuous group of Value

use dense_rank() to rank it by grp find the latest group (r = 1)

min() to get the "first" AsOfDate

select AsOfDate = min(AsOfDate),
       Group_Id
from
(
    select *, r = dense_rank() over (partition by Group_Id, Value 
                                         order by grp desc)
    from
    (
        select *, grp = sum(g) over (partition by Group_Id order by AsOfDate)
        from
        (
            select *, g = case when Value <> lag(Value) over (partition by Group_Id 
                                                                  order by AsOfDate)
                               then 1
                               else 0
                               end
            from   x
        ) x
    ) x
) x
where Value = 1
and   r     = 1
group by Group_Id