How to get closest value from database in mysql

Question

I am using mySQL and CodeIgniter . I have several floating point numbers in my database, for example

• 8.3456
• 8.5555
• 4,5556

I want...

 SELECT * FROM table WHERE value = $myvalue 

but I cannot use value = $myvalue in my SELECT query because $myvalue does not quite match the database values. I need to get the closest $myvalue from the database.

If $myvalue is 5, I want to select a value of 4.5556 .

How to do it in mySQL?

Answer 1

 select * from table order by abs(value - $myvalue) limit 1 

Answer 2

Assuming you have a 10 percent tolerance (+/-), you could try something like:

 select * from table where value >= ($myvalue * .9) and value <= ($myvalue * 1.1) order by abs(value - $myvalue) limit 1 

A slightly updated theft from others - this should return the nearest result in an acceptable range of acceptable values. (Also, I just noticed where it was wrong, apologies - now it should work).

Answer 3

 ( select * from table where value >= $myvalue order by value asc limit 1 ) union ( select * from table where value < $myvalue order by value desc limit 1 ) order by abs(value - $myvalue) limit 1 

This may look counter-intuitive, but the speed will be greater than the other queries shown so far.

This is because the request greater than and less than is faster.

Then doing ABS for two values ​​is nothing.

This will give you quick result in one query that I can think of.

Running ABS on the entire table will be slow since it scans the entire table.

Answer 4

Get the highest value like $ val:

 SELECT * FROM tab WHERE val <= $val ORDER BY val DESC LIMIT 1 

Get the smallest value like $ val:

 SELECT * FROM tab WHERE val >= $val ORDER BY val LIMIT 1 

Get the closest value like $ val in either direction:

 SELECT * FROM tab ORDER BY abs(val - $val) LIMIT 1 

Answer 5

Take the first value from the following:

 select * from table order by abs(value - $myvalue); 

Answer 6

 SELECT * FROM table1 ORDER BY ABS(value - '$myvalue') LIMIT 1 

Answer 7

Read this page http://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html#function_round

but your choice will look like this

 select value from table where ROUND(value) = $myvalue 

Answer 8

Unfortunately, I think your database is likely to do a full table scan for solutions that include abs , so they will be (very) slow as your table grows. A quick fix can be found in this earlier thread .

Answer 9

 SELECT number, ABS( number - 2500 ) AS distance FROM numbers ORDER BY distance LIMIT 6 

Choosing closest values ​​in MySQL

Answer 10

Try the following:

 SELECT *,abs((columnname -Yourvalue)) as near FROM table WHERE order by near limit 0,1 

Answer 11

In my case, I used the geolocation of browsers and tried to find the nearest city / state based on the coordinates that I had in the table.

table structure:

 id zipcode city_state lat lon 1 12345 Example, GA 85.3 -83.2 

I recommend that you thoroughly test this before use - maybe some settings are needed, but I came up with this for a start

 SELECT city_state, zipcode, ( Abs( lat - -33.867886 ) + Abs( lon - -63.987) ) AS distance FROM zipcodes ORDER BY distance LIMIT 1; 

For Laravel users:

 $city = Zipcodes::selectRaw ('city_state, zipcode, ( ABS( lat - ? ) + ABS( lon - ?) ) AS distance', [$lat, $lon]) ->orderBy('distance') ->first(); echo $city->city_state 

Hope this helps someone someday.