Received: from post.webmailer.de (natmail2.webmailer.de [192.67.198.65]) by hub.org (8.10.1/8.10.1) with ESMTP id e6AKJ9W58635 for ; Mon, 10 Jul 2000 16:19:09 -0400 (EDT) Received: from ompc9.dom100.de (p3E9BAB26.dip.t-dialin.net [62.155.171.38]) by post.webmailer.de (8.9.3/8.8.7) with ESMTP id WAA25819; Mon, 10 Jul 2000 22:18:58 +0200 (MET DST) Received: from ompc3.dom100.de [192.168.147.13] (root) by ompc9.dom100.de with esmtp (Exim 2.05 #1 (Debian)) id 13Bjyy-0000cV-00; Mon, 10 Jul 2000 22:16:48 +0200 Received: (from om@localhost) by ompc3.dom100.de (8.9.3/8.8.7) id WAA21325; Mon, 10 Jul 2000 22:14:54 +0200 Date: Mon, 10 Jul 2000 22:14:54 +0200 From: Oliver Mueschke To: pgsql-sql@postgresql.org Cc: tim@lincolnshirenow.com Subject: Re: Re: Matching and Scoring with multiple fields Message-ID: <20000710221454.A16918@ompc3.dom100.de> Mail-Followup-To: pgsql-sql@postgresql.org, tim@lincolnshirenow.com References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Mailer: Mutt 1.0us In-Reply-To: ; from tim@lincolnshirenow.com on Mon, Jul 10, 2000 at 07:56:08PM +0100 X-Archive-Number: 200007/109 I'm not sure, but it seems you could calculate a column like: SELECT a,b,c,..., abs(-a)+abs(-b)+abs(-c)+... AS weight FROM t ORDER BY weight This way the closest matches would come first. On Mon, Jul 10, 2000 at 07:56:08PM +0100, Tim Johnson wrote: > I have a problem. Ok I'll rephrase that, a challenge. > > I have a table like this: > > a,b,c,d,e,f,g,h > --------------- > 2,5,3,4,4,5,2,2 > 1,1,1,1,1,1,1,1 > 5,5,5,5,5,5,5,5 > 3,3,2,4,5,1,1,3 > 1,1,5,5,5,5,1,4 > 1,5,5,5,4,4,2,1 > 5,5,5,5,1,1,1,1 > 1,1,1,1,5,5,5,5 > (rows 8) > > a to h are of type int. > > > I want to take input values which relate to this table say: > how do you feel about a: > how do you feel about b: > how do you feel about c: > ... > > and the answers will be 1 to 5. > > Now I want to take those answers for my incoming a to h and scan down the > table pulling out the closest matches from best to worst. There will be > about 2000 rows in the final table and I will LIMIT the rows in blocks of 10 > or so.