SQL Server Performance Forum – Threads Archive
nearest number
If you have a column, of data type = INT, with the following numbersas 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
quote:Originally posted by spirit1
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
Is this really giving correct results?
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
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, @firstless = 7, @firstGreater = 9
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 select case when @firstLess < @firstGreater then @firstGreater else @firstLess end as myVar
myVar
———–
10 I would probably do something like
SELECT c1
FROM @t
WHERE ABS(c1 – @yourVar)
= (SELECT MIN(ABS(c1 – @yourVar))
FROM @t); c1
———–
7 Although you won’t get good performance on larger tables. —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
Btw, what about:
SELECT TOP 1 c1
FROM @t
ORDER BY ABS([email protected])
—
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
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
quote:Originally posted by Jack Vamvas
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
Nothing magical about it. It Ãs just the absolute value of the difference to get positive and negative differences, that is catch lesser and greater values.
—___________________________________
Need an IT job? –http://www.ITjobfeed.com
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by spirit1</i><br /><br />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><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />As long as it is syntactically permitted. [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
[<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 > t2.c1 – @yourVar THEN t1.c1 ELSE t2.c1 END<br />FROM @t t1<br />CROSS JOIN @t t2<br />WHERE t1.c1 > @yourVar<br />AND t2.c1 < @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]
quote:Originally posted by jhoang_rp
How about this:
select top 1 [col_name], abs([col_name] – @nearestTo) as [diff]
from @t
order by [diff]
This is same as Frank’s second solution
Madhivanan
Failing to plan is Planning to fail
from @t
order by [diff]
]]>