Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id C356E475D74 for ; Wed, 8 May 2002 19:01:38 -0400 (EDT) Received: from mps4.plala.or.jp (mpsb-nat02.plala.or.jp [202.212.114.145]) by postgresql.org (Postfix) with ESMTP id 3B8314762DB for ; Wed, 8 May 2002 18:32:13 -0400 (EDT) Received: from vip1600 ([218.47.116.232]) by mps4.plala.or.jp with ESMTP id <20020508223214.DQAN18083.mps4.plala.or.jp@vip1600>; Thu, 9 May 2002 07:32:14 +0900 Date: Thu, 09 May 2002 07:33:27 +0900 From: Masaru Sugawara To: Sean McCorkle Subject: Re: blanking out repeated columns in rows Cc: pgsql-sql@postgresql.org In-Reply-To: <20020509005131.4FF4.RK73@sea.plala.or.jp> References: <20020507150350.GA24648@avenger.bio.bnl.gov> <20020509005131.4FF4.RK73@sea.plala.or.jp> Errors-To: rk73@sea.plala.or.jp Message-Id: <20020509072256.4921.RK73@sea.plala.or.jp> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Mailer: Becky! ver. 2.00.11 X-Archive-Number: 200205/63 X-Sequence-Number: 7699 On Thu, 09 May 2002 01:51:59 +0900 Masaru Sugawara wrote: > > How about this method of appending sequences as unique indices? > > > First: > create temp sequence dna_rownum1; > create temp sequence dna_rownum2; > > Secand: > SELECT setval('dna_rownum1', 1, false); -- (1) > SELECT setval('dna_rownum2', 1, false); -- (2) > SELECT (CASE WHEN t1.idx = t3.idx THEN t1.tag ELSE NULL END) AS tag, I'm sorry. This one is simpler. SELECT (CASE WHEN t1.oid = t2.idx THEN t1.tag ELSE NULL END) AS tag, t1.gb_id, t1.pos, t1.descrip FROM dna AS t1, (SELECT tag, MIN(oid) AS idx FROM dna GROUP BY tag ) AS t2 WHERE t1.tag = t2.tag ORDER BY t1.tag, t1.oid; Regards, Masaru Sugawara