Find the closest number

Posted on 2011-10-23
Last Modified: 2012-05-12
I am trying to find the closest number to 12345678. I have a table with field `numbers`. I want to search by my number 12345678 and see the closest match
Question by:itudor
    LVL 11

    Assisted Solution

    Something like :

    $match = 33;
    $set = array(1,2,3,5,8,13,21,34,55,89,144,233,377,610);
    foreach ($set as $fib)
        	$diff[$fib] = (int) abs($match - $fib);
    $fibs = array_flip($diff);
    $closest = $fibs[min($diff)];
    echo $closest;

    Open in new window


    Author Comment

    sorry I posted in MySQL not PHP. I want to find the closest number in one mysql query if possible.

    Thank you
    LVL 11

    Assisted Solution

    Found this, there is an oracle statement, so you should be able to adapt that..

    Accepted Solution

    Ok this is the solution:

    SELECT * FROM `table` ORDER BY ABS(`field`-12345678) LIMIT 1

    Open in new window


    Author Closing Comment

    this is the solution
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    yes, that shall work.

    however, in terms of performance, presuming that the field is indexes, this will work much faster, even though more code:

    select * 
     from table
    where `field` = 
     ( select `field` 
         from ( select min(`field`) `field` from `table` where `field` >= 12345678
             union all
                select max(`field`) from `table` where `field` <= 12345678
              ) sq
       order by ABS(`field`-12345678) LIMIT 1

    Open in new window


    Author Comment

    thanks angelIII

    Solution goes to angelII 400 points and comment id 37014647 100 points

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now