From cbm@gfxsp.com Mon Jan  15 17:44:26 2001
Received: from em.gfxsp.com (ABCS.DLCI.26 [206.230.114.60] (may be forged))
	by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0FHiQ089937
	for <pgsql-sql@postgresql.org>; Mon, 15 Jan 2001 12:44:26 -0500 (EST)
	(envelope-from cbm@gfxsp.com)
Received: from cbm1 ([192.168.1.11])
	by em.gfxsp.com (8.9.3/8.8.7) with SMTP id MAA20223
	for <pgsql-sql@postgresql.org>; Mon, 15 Jan 2001 12:46:14 -0500
Message-ID: <002901c07f1a$e4887800$0b01a8c0@graphicfx.net>
From: "Chad McKay" <cbm@gfxsp.com>
To: <pgsql-sql@postgresql.org>
Subject: Sort by relevance
Date: Mon, 15 Jan 2001 12:44:59 -0500
Organization: Graphic FX
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0026_01C07EF0.F8A6B6A0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.50.4133.2400
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400
X-Archive-Number: 200101/147
X-Sequence-Number: 808

This is a multi-part message in MIME format.

------=_NextPart_000_0026_01C07EF0.F8A6B6A0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hello,

I am trying to use a perl script to break an input string into keywords and
pull out all the records that match any of them:

my (@chips)=3D split(' ', $f_name);
foreach my $chip (@chips) {
    next if ($stoppers=3D~/ $chip / || length($chip) < 3);
    if ($query) {$query .=3D " OR name ~* '.*$chip.*' ";}
    else {$query =3D "SELECT * FROM info WHERE name ~* '.*$chip.*' ";}
}

this works fine for pulling the records but I would like to sort them by how
many of the "chips" matched.

On a set like:

name
-------------
Bill Smith
Ted Smith
Ted Brown

an input of : Ted Smith
would give me a query string of:
        SELECT * FROM info WHERE name ~* '.*Ted.*' or name~*'.*Smith.*'

which returns all records - which is what I want - but I would like to have
Ted Smith in the first row followed by the other records grouped by name and
sorted on another column (city or state in the real table)

Thanks for your help,
Chad McKay



------=_NextPart_000_0026_01C07EF0.F8A6B6A0
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 5.50.4134.600" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV>Hello,<BR><BR>I am trying to use a perl script to break an input strin=
g=20
into keywords and<BR>pull out all the records that match any of them:<BR><B=
R>my=20
(@chips)=3D split(' ', $f_name);<BR>foreach my $chip (@chips)=20
{<BR>&nbsp;&nbsp;&nbsp; next if ($stoppers=3D~/ $chip / || length($chip) &l=
t;=20
3);<BR>&nbsp;&nbsp;&nbsp; if ($query) {$query .=3D " OR name ~* '.*$chip.*'=
=20
";}<BR>&nbsp;&nbsp;&nbsp; else {$query =3D "SELECT * FROM info WHERE name ~=
*=20
'.*$chip.*' ";}<BR>}<BR><BR>this works fine for pulling the records but I w=
ould=20
like to sort them by how<BR>many of the "chips" matched.<BR><BR>On a set=20
like:<BR><BR>name<BR>-------------<BR>Bill Smith<BR>Ted Smith<BR>Ted=20
Brown<BR><BR>an input of : Ted Smith<BR>would give me a query string=20
of:<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT * FROM info WHERE =
name=20
~* '.*Ted.*' or name~*'.*Smith.*'<BR><BR>which returns all records - which =
is=20
what I want - but I would like to have<BR>Ted Smith in the first row follow=
ed by=20
the other records grouped by name and<BR>sorted on another&nbsp;column (cit=
y or=20
state in the real table)<BR><BR>Thanks for your help,<BR>Chad=20
McKay<BR><BR></DIV></BODY></HTML>

------=_NextPart_000_0026_01C07EF0.F8A6B6A0--




From serg@city.gov.te.ua Wed Jan  15 07:44:14 2003
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 7EE6C475E42
	for <pgsql-general@postgresql.org>;
	Wed, 15 Jan 2003 02:44:14 -0500 (EST)
Received: from cola.bit.te.ua (unknown [193.193.206.138])
	by postgresql.org (Postfix) with ESMTP id 4CE58475CE7
	for <pgsql-general@postgresql.org>;
	Wed, 15 Jan 2003 02:44:10 -0500 (EST)
Received: from cola.bit.te.ua (root@localhost)
	by cola.bit.te.ua (8.11.6/8.11.6) with SMTP id h0F7fH327459
	for <pgsql-general@postgresql.org>; Wed, 15 Jan 2003 09:41:17 +0200
Received: from city.gov.te.ua (IDENT:1000@mars.l5.city.int [10.0.1.33])
	by cola.bit.te.ua (8.11.6/8.11.6) with ESMTP id h0F7fGW27442;
	Wed, 15 Jan 2003 09:41:16 +0200
Message-ID: <3E251148.8090707@city.gov.te.ua>
Date: Wed, 15 Jan 2003 09:44:08 +0200
From: Serhiy Levchenko <serg@city.gov.te.ua>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.1) Gecko/20020826
X-Accept-Language: ru, uk, en-us, en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org, pgsql-general@postgresql.org
Subject: sort by relevance
X-Enigmail-Version: 0.65.2.0
X-Enigmail-Supports: pgp-inline, pgp-mime
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/881
X-Sequence-Number: 36002

HI, PPL!

How am I able to sort query results by relevance?
I use contrib/tsearch to search using fill text index!

-- 
WBR, sector119




From sector119@mail.ru Wed Jan  15 13:58:44 2003
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 56F94475F6A
	for <pgsql-general@postgresql.org>;
	Wed, 15 Jan 2003 08:58:44 -0500 (EST)
Received: from cola.bit.te.ua (unknown [193.193.206.138])
	by postgresql.org (Postfix) with ESMTP id C7698476297
	for <pgsql-general@postgresql.org>;
	Wed, 15 Jan 2003 08:58:23 -0500 (EST)
Received: from cola.bit.te.ua (root@localhost)
	by cola.bit.te.ua (8.11.6/8.11.6) with SMTP id h0FDtVC00858
	for <pgsql-general@postgresql.org>; Wed, 15 Jan 2003 15:55:31 +0200
Received: from mars.l5.city.int (IDENT:1000@mars.l5.city.int [10.0.1.33])
	by cola.bit.te.ua (8.11.6/8.11.6) with ESMTP id h0FDtVW00843;
	Wed, 15 Jan 2003 15:55:31 +0200
Received: (from sector119@localhost)
	by mars.l5.city.int (8.12.4/8.12.4/Submit) id h0FDwHCE016499;
	Wed, 15 Jan 2003 15:58:17 +0200
X-Authentication-Warning: mars.l5.city.int: sector119 set sender to
	sector119@mail.ru using -f
Date: Wed, 15 Jan 2003 15:58:17 +0200
From: sector119@mail.ru
To: pgsql-sql@postgresql.org
Cc: pgsql-general@postgresql.org
Subject: sort by relevance
Message-ID: <20030115135817.GA16359@city.gov.te.ua>
Mime-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="opJtzjQTFsWo+cga"
Content-Disposition: inline
User-Agent: Mutt/1.4i
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/557
X-Sequence-Number: 35678

--opJtzjQTFsWo+cga
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

HI, PPL!

How am I able to sort query results by relevance?
I use contrib/tsearch to search using fill text index!

--=20
WBR, sector119

--opJtzjQTFsWo+cga
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)

iD8DBQE+JWj5cBYIdwDdknERAlCoAJwO8vsi/u8iyJJzaX9WQq4qPaacTwCgpmhk
OvFcXaU4WJwEG2L+5b6VQVQ=
=Aeib
-----END PGP SIGNATURE-----

--opJtzjQTFsWo+cga--



From oleg@sai.msu.su Wed Jan  15 19:12:49 2003
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id 6C947477131; Wed, 15 Jan 2003 14:12:49 -0500 (EST)
Received: from ra.sai.msu.su (ra.sai.msu.su [158.250.29.2])
	by postgresql.org (Postfix) with ESMTP
	id CE075477436; Wed, 15 Jan 2003 13:19:50 -0500 (EST)
Received: from ra (ra [158.250.29.2])
	by ra.sai.msu.su (8.9.3/8.9.3) with ESMTP id VAA28576;
	Wed, 15 Jan 2003 21:19:47 +0300 (MSK)
Date: Wed, 15 Jan 2003 21:19:47 +0300 (MSK)
From: Oleg Bartunov <oleg@sai.msu.su>
X-X-Sender: megera@ra.sai.msu.su
To: sector119@mail.ru
Cc: pgsql-sql@postgresql.org, <pgsql-general@postgresql.org>
Subject: Re: [SQL] sort by relevance
In-Reply-To: <20030115135817.GA16359@city.gov.te.ua>
Message-ID: <Pine.GSO.4.44.0301152118310.18882-100000@ra.sai.msu.su>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/570
X-Sequence-Number: 35691

On Wed, 15 Jan 2003 sector119@mail.ru wrote:

> HI, PPL!
>
> How am I able to sort query results by relevance?
> I use contrib/tsearch to search using fill text index!
>

Use OpenFTS (openfts.sourceforge.net) for relevance ranking.
we might add relevance feature to tsearch though.


>

	Regards,
		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83




From reedstrm@rice.edu Wed Jan  15 21:00:34 2003
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id 2816947668B; Wed, 15 Jan 2003 16:00:34 -0500 (EST)
Received: from ece.rice.edu (ece.rice.edu [128.42.4.34])
	by postgresql.org (Postfix) with ESMTP
	id CF19747725C; Wed, 15 Jan 2003 15:07:42 -0500 (EST)
Received: from localhost (localhost [127.0.0.1])
	by ece.rice.edu (Postfix) with SMTP
	id 70DA368AA2; Wed, 15 Jan 2003 14:07:43 -0600 (CST)
Received: from localhost (localhost [127.0.0.1])
	by ece.rice.edu (Postfix) with ESMTP
	id 2068868A88; Wed, 15 Jan 2003 14:07:43 -0600 (CST)
Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154])
	by ece.rice.edu (Postfix) with ESMTP
	id 5099068A86; Wed, 15 Jan 2003 14:07:42 -0600 (CST)
Received: from reedstrm by wallace.ece.rice.edu with local (Exim 3.36 #1
	(Debian)) id 18Ytp8-0007dZ-00; Wed, 15 Jan 2003 14:07:42 -0600
Date: Wed, 15 Jan 2003 14:07:41 -0600
From: "Ross J. Reedstrom" <reedstrm@rice.edu>
To: pgsql-sql@postgresql.org, pgsql-general@postgresql.org
Subject: Re: sort by relevance
Message-ID: <20030115200741.GA29352@wallace.ece.rice.edu>
Mail-Followup-To: "Ross J. Reedstrom" <reedstrm@ece.rice.edu>,
	pgsql-sql@postgresql.org, pgsql-general@postgresql.org
References: <20030115135817.GA16359@city.gov.te.ua>
	<Pine.GSO.4.44.0301152118310.18882-100000@ra.sai.msu.su>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <Pine.GSO.4.44.0301152118310.18882-100000@ra.sai.msu.su>
User-Agent: Mutt/1.4i
X-Virus-Scanned: by AMaViS snapshot-20020300
X-DCC--Metrics: ece.rice.edu 1066; Body=1 Fuz1=1 Fuz2=1
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/236
X-Sequence-Number: 11101

On Wed, Jan 15, 2003 at 09:19:47PM +0300, Oleg Bartunov wrote:
> On Wed, 15 Jan 2003 sector119@mail.ru wrote:
> 
> > HI, PPL!
> >
> > How am I able to sort query results by relevance?
> > I use contrib/tsearch to search using fill text index!
> >
> 
> Use OpenFTS (openfts.sourceforge.net) for relevance ranking.
> we might add relevance feature to tsearch though.

Here's another tsearch 'customer' who'd love that feature.

Ross



From sector119@mail.ru Thu Jan  16 06:30:48 2003
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id DF8CF475E42
	for <pgsql-sql@postgresql.org>; Thu, 16 Jan 2003 01:30:48 -0500 (EST)
Received: from cola.bit.te.ua (unknown [193.193.206.138])
	by postgresql.org (Postfix) with ESMTP id 6C75A475C22
	for <pgsql-sql@postgresql.org>; Thu, 16 Jan 2003 01:30:46 -0500 (EST)
Received: from cola.bit.te.ua (root@localhost)
	by cola.bit.te.ua (8.11.6/8.11.6) with SMTP id h0G6UgV11248
	for <pgsql-sql@postgresql.org>; Thu, 16 Jan 2003 08:30:42 +0200
Received: from mars.l5.city.int (IDENT:1000@mars.l5.city.int [10.0.1.33])
	by cola.bit.te.ua (8.11.6/8.11.6) with ESMTP id h0G6UfW11232
	for <pgsql-sql@postgresql.org>; Thu, 16 Jan 2003 08:30:41 +0200
Received: (from sector119@localhost)
	by mars.l5.city.int (8.12.4/8.12.4/Submit) id h0G6UfaO032261
	for pgsql-sql@postgresql.org; Thu, 16 Jan 2003 08:30:41 +0200
X-Authentication-Warning: mars.l5.city.int: sector119 set sender to
	sector119@mail.ru using -f
Date: Thu, 16 Jan 2003 08:30:41 +0200
From: sector119@mail.ru
To: pgsql-sql@postgresql.org
Subject: Re: sort by relevance
Message-ID: <20030116063041.GA32131@city.gov.te.ua>
References: <20030115135817.GA16359@city.gov.te.ua>
	<Pine.GSO.4.44.0301152118310.18882-100000@ra.sai.msu.su>
Mime-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="+QahgC5+KEYLbs62"
Content-Disposition: inline
In-Reply-To: <Pine.GSO.4.44.0301152118310.18882-100000@ra.sai.msu.su>
User-Agent: Mutt/1.4i
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/251
X-Sequence-Number: 11116

--+QahgC5+KEYLbs62
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Wed, Jan 15, 2003 at 09:19:47PM +0300, Oleg Bartunov wrote:
> On Wed, 15 Jan 2003 sector119@mail.ru wrote:
>=20
> > HI, PPL!
> >
> > How am I able to sort query results by relevance?
> > I use contrib/tsearch to search using fill text index!
> >
>=20
> Use OpenFTS (openfts.sourceforge.net) for relevance ranking.

Am I able to use OpenFTS if I save data at db or only if I use txt files??=
=20

> we might add relevance feature to tsearch though.

It would be great to add this feature to tsearch! I need it so much!
Wheh we will be able to see this feature at PostgreSQL CVS? :))

--=20
WBR, sector119

--+QahgC5+KEYLbs62
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)

iD8DBQE+JlGRcBYIdwDdknERAm3HAKClFiPaQxV2FCyxXImiRjDtll1NyQCgpwCF
RRS7Yi4O0hnP0vf+cCoisu0=
=rnng
-----END PGP SIGNATURE-----

--+QahgC5+KEYLbs62--



From mallah@trade-india.com Thu Jan  16 07:09:27 2003
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 4A49D47658D
	for <pgsql-general@postgresql.org>;
	Thu, 16 Jan 2003 02:09:27 -0500 (EST)
Received: from rtlocal.trade-india.com (mail-relay.trade-india.com
	[203.196.129.235])
	by postgresql.org (Postfix) with SMTP id EB415475DB3
	for <pgsql-general@postgresql.org>;
	Thu, 16 Jan 2003 02:07:33 -0500 (EST)
Received: (qmail 331 invoked from network); 16 Jan 2003 07:02:11 -0000
Received: from unknown (HELO system67.trade-india-local.com) (192.168.0.67)
	by infocom-236-129-del.trade-india.com with SMTP;
	16 Jan 2003 07:02:11 -0000
From: "Rajesh Kumar Mallah." <mallah@trade-india.com>
Organization: Infocom Network Limited.
To: Oleg Bartunov <oleg@sai.msu.su>, sector119@mail.ru
Subject: Re: [SQL] sort by relevance
Date: Thu, 16 Jan 2003 12:42:35 +0530
User-Agent: KMail/1.5
Cc: pgsql-sql@postgresql.org, <pgsql-general@postgresql.org>
References: <Pine.GSO.4.44.0301152118310.18882-100000@ra.sai.msu.su>
In-Reply-To: <Pine.GSO.4.44.0301152118310.18882-100000@ra.sai.msu.su>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200301161242.35267.mallah@trade-india.com>
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/946
X-Sequence-Number: 36067


Yet another customer for relevence ranking ;-)

malz.

On Wednesday 15 January 2003 11:49 pm, Oleg Bartunov wrote:
> On Wed, 15 Jan 2003 sector119@mail.ru wrote:
> > HI, PPL!
> >
> > How am I able to sort query results by relevance?
> > I use contrib/tsearch to search using fill text index!
>
> Use OpenFTS (openfts.sourceforge.net) for relevance ranking.
> we might add relevance feature to tsearch though.
>
>
>
>
> 	Regards,
> 		Oleg
> _____________________________________________________________
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

-- 
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.





From bm@turtle-entertainment.de Wed Jan  22 22:30:52 2003
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id B0482477131; Wed, 22 Jan 2003 17:30:52 -0500 (EST)
Received: from ns1.gnw.de (ns1.turtle-entertainment.de [193.41.200.20])
	by postgresql.org (Postfix) with ESMTP
	id 9974B4771C5; Wed, 22 Jan 2003 16:46:32 -0500 (EST)
Received: from [213.148.158.226] (helo=mail.office.turtle-entertainment.de)
	by ns1.gnw.de with esmtp (Exim 3.16 #1 (Debian))
	id 18bSeZ-00082S-00; Wed, 22 Jan 2003 22:43:23 +0100
Received: from pd9e212e9.dip.t-dialin.net ([217.226.18.233] helo=shock)
	by mail.office.turtle-entertainment.de with asmtp (Exim 3.22 #7
	(Debian)) id 18bSeQ-0001Ov-00; Wed, 22 Jan 2003 22:43:14 +0100
Message-ID: <012b01c2c25f$407d2460$0564a8c0@shock>
From: =?iso-8859-1?Q?Bj=F6rn_Metzdorf?= <bm@turtle-entertainment.de>
To: "Serhiy Levchenko" <serg@city.gov.te.ua>,
	<pgsql-sql@postgresql.org>, <pgsql-general@postgresql.org>
References: <3E251148.8090707@city.gov.te.ua>
Subject: Re: sort by relevance
Date: Wed, 22 Jan 2003 22:43:05 +0100
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Scanner: exiscan *18bSeQ-0001Ov-00*nnQA0QBz4ss*
	http://duncanthrax.net/exiscan/
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/885
X-Sequence-Number: 36006

> How am I able to sort query results by relevance?
> I use contrib/tsearch to search using fill text index!

I think you need to use OpenFTS (openfts.sf.net). tsearch does not provide
relevance ranking at this time.

Regards,
Bjoern