SQLite fetch until

I have a question about SQL.

I have the following sample table called Records:

record_id	subject	start_timestamp	end_timestamp	interval
2	Start Product 2	2021-04-21T16:22:39	2021-04-21T16:23:40	0.97
3	error 1	2021-04-21T16:25:44	2021-04-21T16:25:54	10.0
4	End Product 2	2021-04-21T16:30:13	2021-04-21T16:30:14	0.97
5	Start Product 1	2021-04-21T16:35:13	2021-04-21T16:35:13	0.6
6	End Product 1	2021-04-21T16:36:13	2021-04-21T16:36:13	0.45

First I select all the items that have start in there subject with and are not in the table BackupTO (for now the table BackupTO is not important):

SELECT Records.record_id, Records.start_timestamp, Records.interval FROM Records
LEFT JOIN BackupTO ON BackupTO.record_id = Records.record_id
WHERE BackupTO.record_id IS NULL AND Records.log_type = 1 AND Records.subject LIKE '%start%'

When I ran this we get:

record_id	start_timestamp	interval
2	2021-04-21T16:22:39	0.97
5	2021-04-21T16:35:13	0.6

Oke, all good now comes my question, I fetch this in Python and loop through the data, first I calculate the product number based on the interval with:

product = round(result[2] / 0.5)

So a interval of 0.97 is product 2, and a interval of 0.6,0.45 is product 1, all great!

So I know record_id 2 is product 2 and I want to execute a sql query thats returns all items starting from record_id 2 untils its find a items that has %end%2 in its name (the 2 is for product 2, could also be product 1). For example its finds Start Product 2 I get a list with record_id 3 and 4. I want to get all items from the start until end. So it gets me a list like this, this are all the items found under Start Product 2 until %end%2 was found. For product 1, it just would return just record_id 6, because there is nothing between the start and stop.

record_id	start_timestamp	interval
3	2021-04-21T16:22:39	10.0
4	2021-04-21T16:35:13	0.97

I tried OFFSET and FETCH, but I couldnt get it to work, somebody that could help me out here?

Answer

Use your query as a CTE and join it to the table Records.
Then with MIN() window function find the record_id up to which you want the rows returned:

WITH cte AS (
  SELECT r.* 
  FROM Records r LEFT JOIN BackupTO b
  ON b.record_id = r.record_id
  WHERE b.record_id IS NULL AND r.log_type = 1 AND r.subject LIKE '%start%'
)
SELECT *
FROM (
  SELECT r.*, 
         MIN(CASE WHEN r.subject LIKE '%end%' THEN r.record_id END) OVER () id_end
  FROM Records r INNER JOIN cte c
  ON r.record_id > c.record_id
  WHERE c.record_id = ?
)
WHERE COALESCE(record_id <= id_end, 1)

Change ? to 2 or 5 for each case.

If you have the record_ids returned by your query, it is simpler:

SELECT *
FROM (
  SELECT r.*, 
         MIN(CASE WHEN r.subject LIKE '%end%' THEN r.record_id END) OVER () id_end
  FROM Records r
  WHERE r.record_id > ?
)
WHERE COALESCE(record_id <= id_end, 1)

See the demo.