[thelist] Search closest match across multiple columns (mysql)

Dave Stevens evolt at davestevens.co.uk
Wed Apr 16 04:37:57 CDT 2008

Matt Warden wrote:
> Why? We need to know how you determine that.
> Most likely, you are doing something like:
> select name
> from mytable
> order by (height-50)*weight1 + (weight-35)*weight2 +
> (dob-'1981-11-01')*weight3 desc
> weight1, weight2, and weight3 determine how much height, weight, and
> dob matter in determining a match. This is something you have to
> determine. Do you have an idea of these?

That's a good point Matt & Ken, the idea is to locate the person in the 
database most similar in height and weight to the entered data. 
Birthdate would then be used to add an extra level of ordering, so that 
if there were two matches close in height & weight, the closest 
birthdate to the one entered would be the closest match.

I am (and always have been) quite hopeless at mathematics, but I would 
think that in the example above, weight 1 and weight 2 would be similar 
and weight 3 would be lower.

Perhaps it would be beneficial if I spent more time determining the 
mathematical theory of what I am trying to achieve before tackling the 
technical implementation...

Thanks for your responses,

More information about the thelist mailing list