Home > Back-end >  Finding the immediate upper and lower of X
Finding the immediate upper and lower of X

Time:03-12

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

  • Related