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

Matt Warden mwarden at gmail.com
Wed Apr 16 07:24:00 CDT 2008


Dave,

On Wed, Apr 16, 2008 at 5:37 AM, Dave Stevens <evolt at davestevens.co.uk> wrote:
>  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.

Given your description, maybe this would work (also note that I fixed
my goof around the missing absolute value function):

select name
from mytable
order by abs(height-50)+abs(weight-35) desc, abs(dob-'1981-11-01') desc

Now the DOB only comes in to play to break ties. The weights have been
removed because you said height and weight would probably be equally
important.

-- 
Matt Warden
Cincinnati, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.



More information about the thelist mailing list