SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE student ( id SERIAL PRIMARY KEY, State VARCHAR(255) NOT NULL ); INSERT INTO student(State) VALUES ('Suc'), ('Suc'), ('Fail'), ('Suc'), ('Suc'), ('Suc'), ('Fail'), ('Fail'), ('Suc'), ('Suc'); with end_ as ( select * from ( SELECT id, State, case /*when lag(State) over(order by id) = State and lead(State) over(order by id) = State then 'middle' when lag(State) over(order by id) != State or lag(State) over(order by id) is null then 'start' */ when lead(State) over(order by id) != State or lead (State) over(order by id) is null then 'end' else null end as tp from student ) a where tp = 'end' ), start_ as ( select s.* from student s join end_ e on (e.id - s.id = 2 and e.State = s.State) ) select * from student s left join end_ e on e.id = s.id left join start_ st on st.id = s.id where s.id between st.id and e.id