MySQL find nearest matching row

Question

I thought that this would be simple enough, but I just can't get my head around it.

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

Col1 is a straight INT column with values in it incrementing in counts of 20, and starting from 500. So, it has values like 500, 520, 540 and so on. Each of these values map to unique decimal values in Col2.

Now when I run queries, I get values for Col1 that are not in increments of 20. Therefore, I would be asked to find the mapping from Col2 for a value like 524.25. In this case, it should match the value in Col1 for 520 and return the matching decimal value from Col2. If this value was 530 or over, it should match 540 and so on.

Hope this makes sense. Thanks in advance.

Vikram Goyal

Answer 1

You should be able to first sort your rows by the absolute value of the difference (will be lowest for best matching row), and then take Col2 of the first row.

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