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 table_name(ID INT, numbers INT);
Insert Into table_name Values(1,200),
(2,210),
(3,320),
(4,340),
(5,360),
(6,480),
(7,490),
(8,500),
(9,610),
(10,630),
(11,700);
CREATE TABLE
INSERT 0 11
with t as
(
select *,
case when numbers - lag(numbers, 1, numbers) over (order by id) >= 100 then 1 else 0 end as grp
from table_name
)
select id, numbers from t
order by sum(grp) over (order by id) desc, numbers
limit 1
| id | numbers |
|---|---|
| 9 | 610 |
SELECT 1