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

Answer 2

In order to use an index, you could select the minimum value above the target, and the maximum value below. Then you only have two values to check.

Answer 3

How would you handle tie breakers? Because this will only take the first:

  SELECT t.col
    FROM TABLE t
ORDER BY ABS(t.col - @val)
   LIMIT 1

Index safe alternative:

  SELECT xt.col
    FROM (SELECT t.col,
                 ABS(t.col - @val) 'diff'
            FROM TABLE t) xt
ORDER BY xt.diff
   LIMIT 1