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

Matt Warden mwarden at gmail.com
Tue Apr 15 19:07:00 CDT 2008

On Tue, Apr 15, 2008 at 5:49 PM, Dave Stevens <evolt at davestevens.co.uk> wrote:
>  So, for example, assuming my table contains this data:
>  name                 height         weight         dateofbirth
>  Joe Bloggs        60                50                1980-05-02
>  John Smith        70                65                1972-01-17
>  Jane Doe          45                20                 1987-12-14
>  If my user entered:
>  height: 50
>  weight: 35
>  date of birth: 1981-11-01
>  My application would return Jane Doe first, then Joe Bloggs and finally
>  John Smith.

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?

Matt Warden
Cincinnati, OH, USA

This email proudly and graciously contributes to entropy.

More information about the thelist mailing list