scoring select results

From: "Dave [Hawk-Systems]" <dave(at)hawk-systems(dot)com>
To: "PostgreSQL General" <pgsql-general(at)postgresql(dot)org>
Subject: scoring select results
Date: 2003-07-28 19:33:27
Message-ID: DBEIKNMKGOBGNDHAAKGNAEGACNAC.dave@hawk-systems.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

have a table
title, description, keywords
which I am searching (from PHP) using a keyword

What I want to do is sort the results based on the number of hits nd scoring
based on where the hit is. For example, a hit in keywords is worth 5, title is
worth 3, description is worth 1.\

I currently have the following working select;

SELECT *, (
(CASE WHEN (keywords ~* '.*keywordSearch.*') THEN 5 ELSE 0 END) +
(CASE WHEN (title ~* '.*keywordSearch.*') THEN 3 ELSE 0 END) +
(CASE WHEN (description ~* '.*keywordSearch.*') THEN 1 ELSE 0 END)
) AS score FROM catalog_table WHERE
keywords ~* '.*keywordSearch.*' or
title ~* '.*keywordSearch.*' or
description ~* '.*keywordSearch.*'
AND status='D' ORDER BY score DESC

which works great, but the maximum hit is 5 even if a particular item has
multiple hits in several different fields (ie: hit in keyword, title, and
description results in score of 5 instead of score of 8)

1) Any idea on how to rework the query to total the score for all field hits (as
in teh score of 8 we should se from above)?

2) What about multiple hits, for example, two keyword hits, two title hits, and
two description hits totaling a score of 16?

Thanks

Dave

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Momjian 2003-07-28 19:37:47 Re: Using YY-MM-DD date input
Previous Message Jonathan Bartlett 2003-07-28 19:30:11 Re: CREATE TABLE with REFERENCE