Finding the immediate upper and lower of X-CodePudding

For a project, I have a table nearest_values as

id	value
1	8450
2	8500
3	8550
4	8600
5	8650
6	8700

Given a value say 8555, I need to select rows 2 and 3 ie immediately below and above as below.

id	value
2	8500
3	8550

another example for say value 8601 the selected rows should be 4 & 5.

id	value
4	8600
5	8650

CodePudding user response：

Finding a single row above and below value x in a table using SQL. Tested using Postgres 13.

CREATE TABLE example (
  id SERIAL PRIMARY KEY,
  value INT
);

CREATE INDEX example_asc_idx ON example (value ASC);
CREATE INDEX example_desc_idx ON example (value DESC);

INSERT INTO example (id, value)
VALUES (DEFAULT, 8450),
  (DEFAULT, 8500),
  (DEFAULT, 8550),
  (DEFAULT, 8600),
  (DEFAULT, 8700);

WITH above AS (
  SELECT *
  FROM example
  WHERE value > 8555
  ORDER BY value ASC
  LIMIT 1
), below AS (
  SELECT *
  FROM example
  WHERE value < 8555
  ORDER BY value DESC
  LIMIT 1
)
SELECT *
FROM above
UNION ALL
SELECT *
FROM below
ORDER BY id;

Alternately:

SELECT *
FROM example
WHERE id IN ((
  SELECT id FROM example
  WHERE value > 8555
  ORDER BY value ASC
  LIMIT 1
),(
  SELECT id FROM example
  WHERE value < 8555
  ORDER BY value DESC
  LIMIT 1
))
ORDER BY id;

Result for 8555

id	value
3	8550
4	8600

View on DB Fiddle