Lets say we have a table with records populated as under
-- table declaration
declare @t table(num int)
--populate records
insert into @t
select 12 union all select 43
union all select 31 union all select 35
--project records
select *
from @t
/* result
num
-------
12
43
31
35
*/
Next declare a variable to store a number.
--declare a int variable to store a number
declare @inputNum int = 30
We need to find the closest number in the table of supplied number
--program to find the closest number to supplied number
select
DENSE_RANK() over(order by abs(num - @inputNum)) [rank]
,num
from @t
The above program will yield
rank num
---- ----
1 31
2 35
3 43
4 12
We can figure out that using Dense_Rank() function, we are able to rank the numbers properly after finding the distance length [ abs(num - @inputNum) ] between them.
And then finally project the result
select num as [Closest Number] from(
select DENSE_RANK() over(order by abs(num - @inputNum)) [rank]
,num from @t
)X
where [rank] = 1
/*
Closest Number
--------------
31
*/
Let us try out with another example, say @inputNum = 39.
The result is
Closest Number
---------------
43
35
which is correct since the distance length [ abs(num - @inputNum) ] is 4 for both the cases.