SQL program to find the closest number Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 657
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.