Question Details

No question body available.

Tags

sql sqlite

Answers (3)

Accepted Answer Available
Accepted Answer
January 23, 2026 Score: 1 Rep: 10,156 Quality: High Completeness: 50%

What you want to do is to calculate previous and next value before your final x-condition.

select 
from (
select , lag(x) over(order by rowId) as prevX, lead(x)over(order by rowId) as nextX
    from Data
    where level = 2
) x

This gets next and previous X, which you can use in a subquery to make your final selection:

SELECT  
FROM    (
    SELECT  , lag(x) OVER(ORDER BY rowId) AS prevX, lead(x) OVER(ORDER BY rowId) AS nextX
    FROM    Data
    WHERE   level = 2
    ) x
WHERE x between 2 and 4 or prevx between 2 and 4 or nextx between 2 and 4
January 23, 2026 Score: 1 Rep: 80,959 Quality: Medium Completeness: 80%

You can achieve that with UNION:

SELECT value 
FROM mytable 
WHERE x >= 2 AND x  t.ROWID
WHERE t.x < 2 AND t.level == 2 and t2.x is null
union
SELECT t.value 
FROM mytable t
LEFT JOIN my_table t2
ON t2.x = t.x AND t2.level = t.level AND t2.ROWID < t.ROWID
WHERE t.x > 4 AND t.level == 2 and t2.x is null
;

First, we select your "normal" record, then UNION the "previous" one by having the same level as the first one, but x < 2 by trying to find a record in-between and choosing the one that has no such in-between records and then finally UNION with the "following" one by having the same level as the first one, but x > 4 by trying to find a record in-between and choosing the one that has no such in-between records.

Screenshot of the test:

enter image description here

January 23, 2026 Score: 1 Rep: 7,655 Quality: Low Completeness: 100%

To get a generic solution (that will handle holes in your ROWIDs, or values unordered against their ROWID), you have no other choice than:

  1. having an internal position for each row within the level == 2 resultset
    ROW_NUMBER() OVER (ORDER BY ROWID)
  2. in this resultset (with contiguous positions), spot the ones validating x >= 2 AND x = 2 AND x