How to find the most accurate interval?

Question

SQL

Hello.
Tell me the algorithm by which I can find the most accurate interval with a minimum of requests. Perhaps this is already provided in MySQL.
there is a table with segments (start, end) and a value to be found with the most exact occurrence.
For example, there is a table with intervals:

|  id   |        start          |           end      |
|  1    |        10             |         1000       |
|  2    |        100            |          125       |
|  3    |        50             |          3000      |

when searching for the value 101, I would like to get exactly the interval with id=2, because it is the most accurate.
Thanks for the tips!

Answer 1

1. Mathematically formalize the concept of "most accurate", draw up a metric formula.
2. Select all intervals where :value BETWEEN `start` AND `end` and calculate the metric for them.
3. Sort by metric.
4. Select the first row from the sorted list.

Answer 2

select t.id from ranges t 
where &value between t.start and t.end -- сначала отсекаем диапазоны, в которые точно не попадаем
order by t.end - t.start -- определяем "наиболее точный" диапазон как наименьший удовлетворяющий и сортируем по нему в порядке возрастания.

so the first entry will be the most appropriate
working example