nearest number

If you have a column, of data type = INT, with the following numbers
as separate rows.
1
3
4
5
7
10


How can you write a sql statement where you can calculate the
nearest number to any give input.
Example , if the input is 9 ? (the answer is 10)
If the input is 8 (the anwer is 7)

___________________________________
Need an IT job? -http://www.ITjobfeed.com

this is one way:

declare @yourVar int
declare @firstLess int
declare @firstGreater int

select top 1 @firstGreater = MyCol
from MyTable
where > @yourVar
order by id

select top 1 @firstLess = MyCol
from MyTable
where < @yourVar
order by id desc

select case when @firstLess < @firstGreater then @firstGreater else @firstLess end as myVar

_______________________________________________
Causing trouble since 1980
blog:http://weblogs.sqlteam.com/mladenp

Frank

Your first solution was suitable for my purposes, although the second is as useful.I'm trying to understand how the ABS(col1 - @yourVar) works.I will read up on BOL , thanks




___________________________________
Need an IT job? -http://www.ITjobfeed.com

my mistake... read the greatest instead of nearest.

this should do it though:


DECLARE @t TABLE ( c1 INT )
INSERT INTO @t
SELECT 1 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 7 UNION ALL
SELECT 10

DECLARE @yourVar INT
DECLARE @firstLess INT
DECLARE @firstGreater INT
SELECT @yourVar = 8

SELECT TOP 1 @firstGreater = c1
FROM @t
WHERE c1 > @yourVar
ORDER BY c1

SELECT TOP 1 @firstLess = c1
FROM @t
WHERE c1 < @yourVar
ORDER BY c1 DESC

SELECTCASE WHEN @yourVar - @firstLess > @firstGreater - @yourVar
THEN @firstGreater
ELSE @firstLess
END AS myVar


_______________________________________________
Causing trouble since 1980
blog:http://weblogs.sqlteam.com/mladenp

that's quite creative ordering by abs, frank [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />_______________________________________________<br />Causing trouble since 1980<br />blog:<a target="_blank" href=http://weblogs.sqlteam.com/mladenp>http://weblogs.sqlteam.com/mladenp</a>

This should be quicker than Frank's original solution:

SELECT CASE
WHEN ABS(firstlower.i - @i) < ABS(firsthigher.i - @i) THEN firstlower.i
WHEN ABS(firstlower.i - @i) > ABS(firsthigher.i - @i) THEN firsthigher.i
ELSE COALESCE(firsthigher.i, firstlower.i) END
FROM
(select MAX(t1.int_column) i from mytable t1 where t1.int_column <= @i) firstlower,
(select MIN(t2.int_column) i from mytable t2 where t2.int_column >= @i) firsthigher

[<img src='/community/emoticons/emotion-1.gif' alt='' />][<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />well just for the fun of it, here another one:<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECTTOP 1 CASE WHEN @yourVar - t1.c1 &gt; t2.c1 - @yourVar THEN t1.c1 ELSE t2.c1 END<br />FROM @t t1<br />CROSS JOIN @t t2<br />WHERE t1.c1 &gt; @yourVar<br />AND t2.c1 &lt; @yourVar<br />ORDER BY t2.c1 DESC<br /></font id="code"></pre id="code"><br /><br /><br />_______________________________________________<br />Causing trouble since 1980<br />blog:<a target="_blank" href=http://weblogs.sqlteam.com/mladenp>http://weblogs.sqlteam.com/mladenp</a>

How about this:

select top 1 [col_name], abs([col_name] - @nearestTo) as [diff]
from @t
order by [diff]