Search code examples
sqlproximity

SQL - find row with values of two columns closest to X and Y

I am building a website on wich people can get some infos based on their weight and height. How would one structure a query that would give me a row with the two specific values closest to the ones users enter?

I found this on stackoverflow and it was very helpful. I am trying to accomplish something similar to that only with 2 values insted of 1.

Solution

  • If the weight and height are of equal importance, you can use this (linear)

    select top 1 *
from tbl
order by ABS(weight-@weight) + ABS(height-@height)

    A better option could be to weigh the differences, on a scale, such as making 0.01m (1cm) of height of equal importance as 1kg. Square both sides as well, so that a deviation of 5cm and 5kg is seen as "closer" than 10cm and 0kg.

    (Assuming all inputs are in kg and metres)

    select top 1 *
from tbl
order by ABS(weight-@weight)^2 + (ABS(height-@height)*100)^2