Solved

# Find the closest number

Posted on 2011-10-23
352 Views
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
0
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;
``````
0

Author Comment

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

Thank you
0

LVL 11

Assisted Solution

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

http://stackoverflow.com/questions/445782/finding-closest-match-in-collection-of-numbers
0

Accepted Solution

Ok this is the solution:

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

Author Closing Comment

this is the solution
0

LVL 142

Expert Comment

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
)
``````
0

Author Comment

thanks angelIII

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

## Join & Write a Comment Already a member? Login.

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 (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. 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.

#### Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!