Solved

# Find the closest number

Posted on 2011-10-23
Medium Priority
354 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
• 4
• 2

LVL 11

Assisted Solution

maeltar earned 1200 total points
ID: 37014611
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

ID: 37014622
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

maeltar earned 1200 total points
ID: 37014647
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

itudor earned 0 total points
ID: 37014700
Ok this is the solution:

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

Author Closing Comment

ID: 37043485
this is the solution
0

LVL 143

Expert Comment

ID: 37016254
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

ID: 37017245
thanks angelIII

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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question