SQL Server Performance

nearest number

Discussion in 'SQL Server 2005 General Developer Questions' started by Jack Vamvas, Jul 6, 2007.

  1. Jack Vamvas Member

    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

  2. spirit1 New Member

    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
  3. FrankKalis Moderator

    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 @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
  4. FrankKalis Moderator

  5. Jack Vamvas Member

    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

  6. spirit1 New Member

    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
  7. spirit1 New Member

    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>
  8. Adriaan New Member

    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
  9. FrankKalis Moderator

    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.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de
  10. FrankKalis Moderator

    <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>
  11. spirit1 New Member

    [<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>
  12. jhoang_rp New Member

    How about this:

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

    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

Share This Page