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

[Antonio Angelo]

Hi Dave!

Programs that performs optimization, iterates to minimize an error function.
In my opinion, to find the closest set of data you should work in a
similar way, by defining your error function (e.g. by summing weighted
squared differences respect the searched values), the sorting by that
error function.

In your example, the error function could be like that:

SELECT name, height, weight, dateofbirth,
, w1*(weight - specifiedWeight)^2 + w2*(height - specifiedHeight)^2  +
w3*DATEDIFF(dateofbirth, sepcifiedDate)^2 AS erf
FROM...
ORDER BY erf

Good work!
--
Antonio Angelo

2008/4/15, Dave Stevens <evolt at davestevens.co.uk>:
> Hi list,
>
>  I have a database table which contains basic biographic data about
>  various people (height, weight, date of birth). I then have a form that
>  allows a user to input a specific value for each of these pieces of data
>  and need to return the closest matching people in the table, with
>  closest match first.
>
>  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.
>
>  I'm using MySQL as my database and while I approached this problem
>  confidently, I've not had any luck in solving it adequately.
>
>  Can anyone offer any pointers?
>
>  Thanks,
>  Dave
>
>
>
>  --
>
>  * * Please support the community that supports you.  * *
>  http://evolt.org/help_support_evolt/
>
>  For unsubscribe and other options, including the Tip Harvester
>  and archives of thelist go to: http://lists.evolt.org
>  Workers of the Web, evolt !
>


-- 
Antonio Angelo