By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table mytable (number int, status varchar(10));
insert into mytable values( 1 ,'g');
insert into mytable values( 2 ,'a');
insert into mytable values(3 ,'b');
insert into mytable values(4 ,'c');
insert into mytable values(5 ,'d');
insert into mytable values(6 ,'e');
insert into mytable values(7 ,'f');
insert into mytable values(8 ,'b');
insert into mytable values(9 ,'e');
insert into mytable values(10 , 'a');
insert into mytable values(11 ,'c');
insert into mytable values(12 ,'f');
insert into mytable values(13 ,'g');
insert into mytable values(14 ,'d');
insert into mytable values(15 ,'a');
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
with cte as
(
select number,status,(case when status='a' then grp else lag(grp) over(ORDER BY NUMBER)end)GRP from
(
select *, lead(status)over(order by number)nextStatus,lag(status)over(order by number)prevStatus, number grp from mytable
where status='a'or status='f'
)t
where (status='a' and nextStatus='f') or (status='f' and prevStatus='a')
)
select m.number,m.status from mytable m inner join
(
select min(number) strt, max(number) fnsh
from cte
group by grp
)t
on m.number between t.strt and t.fnsh order by number
| number | status |
|---|---|
| 2 | a |
| 3 | b |
| 4 | c |
| 5 | d |
| 6 | e |
| 7 | f |
| 10 | a |
| 11 | c |
| 12 | f |