[thelist] Search closest match across multiple columns (mysql)
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):
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
Cincinnati, OH, USA
This email proudly and graciously contributes to entropy.
More information about the thelist