MySQL finds the closest matching string

Question

I thought it would be easy enough, but I just can't get around it.

I have a display / lookup table with two columns. Col1 is used to find values ​​from Col2.

Col1 is a direct INT column with values ​​in it increasing by 20 and starting at 500. Thus, it has values ​​such as 500, 520, 540 and so on. Each of these values ​​displays unique decimal values ​​in Col2.

Now, when I run the queries, I get the values ​​for Col1, which are not increments of 20. Therefore, I will be asked to find a mapping from Col2 for a value such as 524.25. In this case, it must match the value in Col1 for 520 and return the corresponding decimal value from Col2. If this value was 530 or more, it should correspond to 540, etc.

Hope this makes sense. Thanks in advance.

Vikram Goal

Answer 1

You must first sort your lines by the absolute value of the difference (which will be the lowest for the best match line), and then take Col2 first line.

 SELECT Col2 FROM your_table ORDER BY ABS( Col1 - your_value ) LIMIT 1