[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