X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 5CE999DC84A for ; Fri, 10 Feb 2006 02:02:47 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 37490-07 for ; Fri, 10 Feb 2006 02:02:46 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id A5C499DC818 for ; Fri, 10 Feb 2006 02:02:44 -0400 (AST) Received: (qmail 26497 invoked by uid 500); 10 Feb 2006 06:11:29 -0000 Date: Fri, 10 Feb 2006 00:11:29 -0600 From: Bruno Wolff III To: superboy143 Cc: pgsql-sql@postgresql.org Subject: Re: query Message-ID: <20060210061129.GA26002@wolff.to> Mail-Followup-To: Bruno Wolff III , superboy143 , pgsql-sql@postgresql.org References: <2798161.post@talk.nabble.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <2798161.post@talk.nabble.com> User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.092 required=5 tests=[AWL=0.092] X-Spam-Score: 0.092 X-Spam-Level: X-Archive-Number: 200602/110 X-Sequence-Number: 24120 On Tue, Feb 07, 2006 at 01:45:50 -0800, "superboy143 (sent by Nabble.com)" wrote: > > I have a table in which I have a field with format like 100101. It has many values like 100101, 100102, 100103, 100201, 100202, 100301. I have to write a query such that I have to get only distinct values such that they contain only the substring I need. If I give 10 as substring, then it should return only 100101 or 100102 but not both i.e if the last two characters are not same it should not return both of them. It should return only values starting with 10 the middle two values should be distinct and the last two characters may be anything. You can probably use the Postgres extension DISTINCT ON to do what you want.