[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