sql - Select closest numerical value with MySQL query

Question

This is probably easier than I am making it, but basically what I need to do is select the row that has the closest number in a column as a specified value. For example:

List of values in database for 3 rows in a specified column: 10, 15, 16

If I specify that I want the row that is closest to 14, it would pick the row with 15.

Also, if there are 2+ rows that are the same distance, pick one of them randomly.

Answer 1

One option would be something along the lines of:

select   the_value,
         abs(the_value - 14) as distance_from_test
from     the_table
order by distance_from_test
limit 1

To select a random record, you can add , rand() to the order by clause. The disadvantage of this method is that you don't get any benefit from indices because you have to sort on the derived value distance_from_test.

If you have an index on the_value and you relax your requirement for the result to be random in the case of ties, you can perform a pair of limited range queries to select the first value immediately above the test value and the first value immediately below the test value and pick whichever is closest to the test value:

(
select   the_value
from     the_table
where    the_value >= 14
order by the_value asc
limit 1
)
union
(
select   the_value
from     the_table
where    the_value < 14
order by the_value desc
limit 1
)
order by abs(the_value - 14)
limit 1