I have one table, example:
| ID | numbers |
| -------- | -------- |
| 1 | 200 |
| 2 | 210 |
| 3 | 320 |
| 4 | 340 |
| 5 | 360 |
| 6 | 480 |
| 7 | 490 |
| 8 | 500 |
| 9 | 610 |
| 10 | 630 |
| 11 | 700 |
Everywhere where the number change more than 100 ( ID 3,(minimum 200 on ID 1 and ID 3 320 more than 100 ) so restart the minimum search) need to find the minimum after the jump.
first minimum ID 1 after have one jump on ID 3 then restart to search the minimum and the current minimum is 320 on ID 3, after again restart the search have one jump on ID 6 and restart the minimum search the minimum is 480 on ID 6, then jump again on ID 9, so restart the min search and the current minimum is 630 on ID 9.
General minimum search not take the highest number just after, I cannot set fix number in the code ( like ID number ) because later change. I have the query what is find the more than 100 jump, and restart the minimum search.
select pos.min(numbers) as minimumtemp from example_table;
Expected output in this case is minimum = 610
Any solution is help thank you!
One option is to use a running sum that increased by 1 whenever the value of the numbers jumps by 100+:
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