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
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 |