how to get nearest value from database in mysql

Question

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 $myvalue is 5 I want to select the value 4.5556.

How can I do this in mySQL?

Answer 1

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

Answer 2

(
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.