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

Dave Stevens evolt at davestevens.co.uk
Wed Apr 16 10:51:27 CDT 2008


Phil Turmel wrote:
> Hmmm.  Let me amend this....  You'll want absolute values on each component,
> and an ascending index.
> 
> Something like this:
> 
> SELECT name
>   FROM  mytable
>   ORDER BY ABS(height-50)*W1 + ABS(weight-35)*W2 + ABS(dob-'1981-11-01')*W3
>   LIMIT 10
> 
> You can get starting points for W1, W2, and W3 with:
> 
> SELECT 1/STD(height) AS W1,
>   1/STD(weight) AS W2,
>   1/STD(dob-'2000-01-01') AS W3
> 
> HTH,
> 
> Phil

Thanks Phil and Matt - I'm now getting back exactly the data I would 
hope, I really appreciate your help on this.

Cheers,
Dave



More information about the thelist mailing list