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 5DCAA9DC898
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 10 Feb 2006 12:21:42 -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 11229-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 10 Feb 2006 12:21:40 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from rwcrmhc14.comcast.net (rwcrmhc14.comcast.net [216.148.227.154])
	by postgresql.org (Postfix) with ESMTP id CC3959DC891
	for <pgsql-sql@postgresql.org>; Fri, 10 Feb 2006 12:21:38 -0400 (AST)
Received: from [10.0.1.5] (c-24-20-199-254.hsd1.or.comcast.net[24.20.199.254])
	by comcast.net (rwcrmhc14) with SMTP
	id <20060210162137m1400dh58pe>; Fri, 10 Feb 2006 16:21:37 +0000
Subject: Re: query
From: Ken Hill <ken@scottshill.com>
Reply-To: ken@scottshill.com
To: Bruno Wolff III <bruno@wolff.to>
Cc: superboy143 <superboy143@gmail.com>, pgsql-sql@postgresql.org
In-Reply-To: <20060210061129.GA26002@wolff.to>
References: <2798161.post@talk.nabble.com> <20060210061129.GA26002@wolff.to>
Content-Type: multipart/alternative; boundary="=-UUju+pOqUEQ86skafFAs"
Organization: Scotts Hill Associates, Inc.
Date: Fri, 10 Feb 2006 08:22:03 -0800
Message-Id: <1139588523.29808.10.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.4.1 
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.066 required=5 tests=[AWL=0.065,
 HTML_MESSAGE=0.001]
X-Spam-Score: 0.066
X-Spam-Level: 
X-Archive-Number: 200602/114
X-Sequence-Number: 24124


--=-UUju+pOqUEQ86skafFAs
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Fri, 2006-02-10 at 00:11 -0600, Bruno Wolff III wrote:

> On Tue, Feb 07, 2006 at 01:45:50 -0800,
>   "superboy143 (sent by Nabble.com)" <lists@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.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq


Try substring(column,1,4). That should return values of 1001, 1002, 1003
when grouped.

--=-UUju+pOqUEQ86skafFAs
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.8.1">
</HEAD>
<BODY>
On Fri, 2006-02-10 at 00:11 -0600, Bruno Wolff III wrote:
<BLOCKQUOTE TYPE=CITE>
<PRE>
<FONT COLOR="#000000">On Tue, Feb 07, 2006 at 01:45:50 -0800,</FONT>
<FONT COLOR="#000000">  &quot;superboy143 (sent by Nabble.com)&quot; &lt;<A HREF="mailto:lists@nabble.com">lists@nabble.com</A>&gt; wrote:</FONT>
<FONT COLOR="#000000">&gt; </FONT>
<FONT COLOR="#000000">&gt; 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.</FONT>

<FONT COLOR="#000000">You can probably use the Postgres extension DISTINCT ON to do what you want.</FONT>

<FONT COLOR="#000000">---------------------------(end of broadcast)---------------------------</FONT>
<FONT COLOR="#000000">TIP 3: Have you checked our extensive FAQ?</FONT>

<FONT COLOR="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <A HREF="http://www.postgresql.org/docs/faq">http://www.postgresq</FONT>l.org/docs/faq</A>
</PRE>
</BLOCKQUOTE>
<BR>
Try substring(column,1,4). That should return values of 1001, 1002, 1003 when grouped.
</BODY>
</HTML>

--=-UUju+pOqUEQ86skafFAs--