nearest number

Discussion in 'MySQL' started by cancer10, Mar 8, 2008.

0
  1. #1
    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)
     
    cancer10, Mar 8, 2008 IP
  2. AstarothSolutions

    AstarothSolutions Peon

    Messages:
    2,680
    Likes Received:
    77
    Best Answers:
    0
    Trophy Points:
    0
    #2
    You dont say which SQL you are using.... with SQL Server it would be something like:
    
SELECT     TOP (1) NumberCol
FROM         NearestNumber
ORDER BY ABS(NumberCol - @Query);
    Code (sql):
     
    AstarothSolutions, Mar 8, 2008 IP
  3. cancer10

    cancer10 Guest

    Messages:
    364
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    I use mysql
     
    cancer10, Mar 8, 2008 IP
  4. AstarothSolutions

    AstarothSolutions Peon

    Messages:
    2,680
    Likes Received:
    77
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Well Top (1) becomes Limit (0,1) from memory, ABS() is the same in both and as your also probably a php person you will replace the @Query parameter with inline insertion of the number
     
    AstarothSolutions, Mar 8, 2008 IP
  5. kmap

    kmap Well-Known Member

    Messages:
    2,215
    Likes Received:
    29
    Best Answers:
    2
    Trophy Points:
    135
    #5
    "SELECT `NumberCol` FROM `NearestNumber` ORDER BY ABS(NumberCol -$n) limit(0,1)";

    i think this is what he means and full marks

    Regards

    alex
     
    kmap, Mar 14, 2008 IP