Selecting items before/after match
Create test data
-- CREATE TABLE test (x SERIAL, y INT); INSERT INTO test (y) VALUES (64), (48), (32), (16), (50), (40), (30), (20), (10), (60), (70), (80); --
View data
-- SELECT * FROM test; x | y ----+---- 1 | 64 2 | 48 3 | 32 4 | 16 5 | 50 6 | 40 7 | 30 8 | 20 9 | 10 10 | 60 11 | 70 12 | 80 --
With respect to x-ordering, get y-value of the item before and the item after the item with y=50:
-- SELECT * FROM ( SELECT x, lag(y,1,0) OVER (ORDER BY x) AS y_prev, y AS y_this, lead(y,1,0) OVER (ORDER BY x) AS y_next FROM test) AS tmp WHERE this = 50; | x | y_prev | y_this | y_next | +---+--------+--------+--------+ | 5 | 16 | 50 | 40 | --