?
Solved

Find the closest number

Posted on 2011-10-23
7
Medium Priority
?
354 Views
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
0
Comment
Question by:itudor
  • 4
  • 2
7 Comments
 
LVL 11

Assisted Solution

by:maeltar
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;

Open in new window

0
 

Author Comment

by:itudor
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

by:maeltar
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Accepted Solution

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

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

Open in new window

0
 

Author Closing Comment

by:itudor
ID: 37043485
this is the solution
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
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
 )

Open in new window

0
 

Author Comment

by:itudor
ID: 37017245
thanks angelIII

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month17 days, 8 hours left to enroll

830 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