Re: [SQL] Weighted Searching

I'm not sure how fast it is, but something like this
would work, right?

WHERE
(CASE WHEN degree='MBA' THEN 10 ELSE 0 END +
CASE WHEN years_experience='5' THEN 10 ELSE 0 END _
CASE WHEN location_state='Arizona' THEN 10 ELSE 0 END)
>=20

Also, wouldn't you usually want to be searching where the
weight was given if you had the years_experience or greater?

Stephan Szabo
sszabo@bigpanda.com

On Tue, 12 Sep 2000, Mitch Vincent wrote:

> I emailed the list a while back about doing some weighted searching, asking
> if anyone had implemented any kind of weighted search in PostgreSQL.. I'm
> still wondering the same thing and if anyone has, I would greatly appreciate
> a private email, I'd like to discuss it in detail.. I have several ideas but
> most of them are pretty dirty and slow..
>
> What I need to do is allow the user to assign weights to fields and then
> specify a minimum weight which would dictate results..
>
> Example :
>
> A search on two fields, degree and years_experience, location_state.
>
> The user assigns degree a weight of 10, years_experience a weight of 10 and
> location_state a weight of 10. Then specifies the minimum weight as 20,
> meaning that any results returned would have to have at least two of the
> fields an exact match (any two that where the sum of the weight equals 20).
> This could be carried out to many, many fields and extremely high weights..
>
> The problem I'm having is figuring out a good way to assign the weights to
> individual fields and test to see if an individual field is exactly matched
> in the query (without running a single query for each field searched on.
>
> Example:
>
> The SQL query for the search above might be :
>
> SELECT * FROM people WHERE degree='MBA' and years_experience='5' and
> location_state='Arizona'
>
> I would want people that have an MBA and 5 years experience but they
> wouldn't necessarily have to be in Arizona (because our minimum weight is
> 20, only two would have to match)..
>
> Hopefully I'm not over-explaining to the point of confusion.. If anyone
> would have any ideas, please drop me an email.. Thanks!!!
>
> -Mitch
>
>
>
>
>