Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id BF1249FBFA5 for ; Fri, 10 Aug 2007 04:59:55 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 94227-09 for ; Fri, 10 Aug 2007 04:59:51 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from depesz.com (depesz.com [88.198.37.40]) by postgresql.org (Postfix) with ESMTP id 94BA99FBB49 for ; Fri, 10 Aug 2007 04:59:51 -0300 (ADT) Received: from depesz by depesz.com with local (Exim 4.63) (envelope-from ) id 1IJPPR-0008ML-LR; Fri, 10 Aug 2007 09:59:49 +0200 Date: Fri, 10 Aug 2007 09:59:49 +0200 From: hubert depesz lubaczewski To: Kiran Cc: pgsql-sql@postgresql.org Subject: Re: Best Fit SQL query statement Message-ID: <20070810075949.GA31829@depesz.com> Reply-To: depesz@depesz.com References: <1186390627.045596.148460@j4g2000prf.googlegroups.com> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline In-Reply-To: <1186390627.045596.148460@j4g2000prf.googlegroups.com> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/75 X-Sequence-Number: 29013 On Mon, Aug 06, 2007 at 01:57:07AM -0700, Kiran wrote: > Could anyone help me in writing Best Fit SQL statement. > Suppose we have table t1 with coloumn t1 (text) with following rows. > 98456 > 98457 > 9845 > 9846 > 984 > 985 > 98 > 99 > and if I query on 98456 the result must be 98456, > However if I query on 98455 the result must be 9845 > and If I query 9849 the result must be 984 select t1.t1 from t1 where '98456' like t1.t1||'%' order by length(t1.t1) desc limit 1; should be ok. depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV)