Re: Rank based on the number of matching OR fields? - Mailing list pgsql-general

From Joel Hoffman
Subject Re: Rank based on the number of matching OR fields?
Date
Msg-id CAEF8rJv9XG=3F3C9PZMqqPSqALmrX3Rqtx+1H=o7d9KPnomZgA@mail.gmail.com
Whole thread Raw
In response to Rank based on the number of matching OR fields?  ("W. Matthew Wilson" <matt@tplus1.com>)
List pgsql-general
If you're easily able to do it, (i.e. you're building rather than receiving the query), you could rank them by the conjunction of the search terms first:

ORDER BY ts_rank(vector, to_tsquery('A & B & C')) desc, ts_rank(vector, to_tsquery('A | B | C')) desc

Or just explicitly order by whether the conjunction matches:

ORDER BY case when to_tsquery('A & B & C') @@ vector then 0 else 1 end, ts_rank(vector, to_tsquery('A | B | C')) desc

I think either of these would have the property you want, but I don't know how they would otherwise affect the quality of the ranking.  You should set up a test group of documents and make sure your mechanism ranks that group properly on test queries.

Joel

On Tue, Sep 25, 2012 at 11:16 AM, W. Matthew Wilson <matt@tplus1.com> wrote:
I want to run a query like to_tsquery("A | B | C") and then rank the
results so that if a document contained A, B, and C, then it would
rank above a document that just had some subset.

How would I do such a thing?

--
W. Matthew Wilson
matt@tplus1.com
http://tplus1.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

pgsql-general by date:

Previous
From: Scot Kreienkamp
Date:
Subject: Re: idle in transaction query makes server unresponsive
Next
From: John R Pierce
Date:
Subject: Re: idle in transaction query makes server unresponsive