How to Find Minimum and Maximum Values Across Consecutive Rows with Gaps and Islands?

Finding Minimum and Maximum Values for Consecutive Rows with Gaps and Islands

The goal is to extract minimum and maximum values of startt and endd columns for consecutive rows sharing the same act value, while ignoring gaps and islands of rows with different act values.

Solution:

To achieve this, we utilize row numbers to identify consecutive groups of similar rows:

with cte as (
  select name, act, rn, startt, endd,
         row_number() over (partition by name, act order by rn) as act_rn
  from input
)
select name, act, min(startt), max(endd)
from cte
group by name, act, act_rn - rn;

Explanation:

• The subquery cte adds row numbers and act row numbers to the input data.
• The main query groups the data by name, act, and the difference between act row number and row number. This difference is used to differentiate between consecutive and non-consecutive rows within a particular act value.
• The grouping allows us to aggregate minimum and maximum values for each consecutive group, eliminating gaps and islands.

The above is the detailed content of How to Find Minimum and Maximum Values Across Consecutive Rows with Gaps and Islands?. For more information, please follow other related articles on the PHP Chinese website!