# SQL program to find the closest number Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 1137
Lets say we have a table with records populated as under

`-- table declarationdeclare @t table(num int)--populate recordsinsert into @t select 12 union all select 43 union all select 31 union all select 35	--project recordsselect *from @t/* resultnum-------12433135*/`

Next declare a variable to store a number.

`--declare a int variable to store a numberdeclare @inputNum int = 30`

We need to find the closest number in the table of supplied number

`--program to find the closest number to supplied numberselect 	DENSE_RANK() over(order by abs(num - @inputNum)) [rank]			,num from @t`

The above program will yield

`rank	num----	----1	312	353	434	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)Xwhere [rank] = 1/*Closest Number--------------31*/`

Let us try out with another example, say @inputNum = 39.

The result is

`Closest Number---------------4335`

which is correct since the distance length [ abs(num - @inputNum) ] is 4 for both the cases.