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

Question

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.

Answer 1

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

Answer 2

Rather than trying to do some clever SQL tricks to accomodate this, why not restrict user input (using drop down lists, rounding to nearest value, etc.) to match the values available in your dataset.

Answer 3

What about the averaged difference of the two? I would recommend giving the difference in height a higher weight since 6" of difference is much more drastic than 6 lbs of difference. Example:

SELECT * FROM table
ORDER BY ABS(weight - @weight) + ABS(height - @height)*3

This is just me taking a swing at trying to make the difference in height more important. In this example I am giving it a weight of 3. This example assumes a person's height is in inches... If you have it in centimeters or something it might work better.