Playing with PostgreSQL: Window functions

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 |

--