how to get nearest value from database in mysql
I am using mySQL
and CodeIgniter
. I have some floating point numbers in my database such as
- 8.3456
- 8.5555
- 4.5556
I want to...
SELECT * FROM table WHERE value = $myvalue
but I can't use value = $myvalue
in my SELECT query because $myvalue
is not exactly equal to database values. I need to get the nearest value to $myvalue
from database.
If 开发者_C百科$myvalue
is 5 I want to select the value 4.5556
.
How can I do this in mySQL?
select *
from table
order by abs(value - $myvalue)
limit 1
Assuming that you have a 10% 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
Slightly updated stealing from others - this should return the nearest result in the assumed tolerance range. (Also, I just noticed the where was incorrect, apologies - now it should work).
(
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 due to the fact that a greater than
and less than
query is quicker.
Then doing an ABS
on two values is nothing.
This will give you the quickest return in a single query I can think of.
Doing an ABS
on a whole table will be slow as it will scan the whole table.
Take the first value from the following:
select * from table order by abs(value - $myvalue);
Get the largest value similar to $val:
SELECT * FROM tab WHERE val <= $val ORDER BY val DESC LIMIT 1
Get the smallest value similar to $val:
SELECT * FROM tab WHERE val >= $val ORDER BY val LIMIT 1
Get the closest value similar to $val in either direction:
SELECT * FROM tab ORDER BY abs(val - $val) LIMIT 1
In my case, I was using the browsers geolocations and trying to find a closest city/state based on the coordinates I had in a table.
table structure:
id zipcode city_state lat lon
1 12345 Example, GA 85.3 -83.2
Recommend testing this vigorously before using -- probably needs some tweaks, but I came up with this as 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.
SELECT * FROM table1 ORDER BY ABS(value - '$myvalue') LIMIT 1
SELECT number, ABS( number - 2500 ) AS distance
FROM numbers
ORDER BY distance
LIMIT 6
Selecting closest values in MySQL
Try this:
SELECT *,abs((columnname -Yourvalue)) as near
FROM table
WHERE order by near limit 0,1
Unfortunately, I think your database will probably do a full table scan for solutions that involve abs
, so they will be (very) slow once your table grows. A fast-running solution may be found in this earlier thread specific to PostgreSQL.
Read this page http://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html#function_round
but your select would look like this
select value from table where ROUND(value) = $myvalue
精彩评论