[thelist] Search closest match across multiple columns (mysql)
Antonio Angelo
antonio.angelo at gmail.com
Wed Apr 16 16:15:41 CDT 2008
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
More information about the thelist
mailing list