How to find the first nearest value up and down in SQL?

Question

I have table like this:

When I set id =4 , the results I need are:

Note that I selected the ID=4 and the two nearest values from both sides .

How can write sql code for do it?

Answer 1

You can use LEAD() function , that selects the next value by a specific order , like this:

Note that LEAD was only introduced to SQL-Server 2012+.

SELECT s.id,s.name,s.number
FROM (
    SELECT t.*
           LEAD(t.id,1) OVER(ORDER BY t.Number DESC) as Next_val,
           LEAD(t.id,1) OVER(ORDER BY t.Number) as Last_val
    FROM YourTable t) s
WHERE 4 IN(s.id,next_Val,s.last_val)

You can replace 4 with your desired ID or with a parameter .

EDIT: A little explanation - LEAD function provides a way to access the next row, without the use of a SELF JOIN or a sub query , it orders the results by the order you provided inside the OVER() clause, and select the value inside the parentheses - LEAD(value) that belong to the record above the current record that is being processed. So, this query selects each ID , and the ID that belongs to the nearest value up and down , and then check if one of them is your desired ID .