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

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


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

Matt Warden
Cincinnati, OH, USA

This email proudly and graciously contributes to entropy.

More information about the thelist mailing list