Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id E57E9476642 for ; Wed, 8 May 2002 13:42:48 -0400 (EDT) Received: from mps5.plala.or.jp (mpsb-nat18.plala.or.jp [202.212.115.65]) by postgresql.org (Postfix) with ESMTP id 65C1F476501 for ; Wed, 8 May 2002 12:50:45 -0400 (EDT) Received: from vip1600 ([218.47.116.232]) by mps5.plala.or.jp with ESMTP id <20020508165046.EAQZ12874.mps5.plala.or.jp@vip1600>; Thu, 9 May 2002 01:50:46 +0900 Date: Thu, 09 May 2002 01:51:59 +0900 From: Masaru Sugawara To: mccorkle@bnl.gov Subject: Re: blanking out repeated columns in rows Cc: pgsql-sql@postgresql.org In-Reply-To: <20020507150350.GA24648@avenger.bio.bnl.gov> References: <20020507150350.GA24648@avenger.bio.bnl.gov> Errors-To: rk73@sea.plala.or.jp Message-Id: <20020509005131.4FF4.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/55 X-Sequence-Number: 7691 On Tue, 7 May 2002 11:03:50 -0400 Sean McCorkle wrote: > but I (and my colleagues) would much rather see this, which draws > attention to the duplicates (or multiples) in the left column. > > tag gb_id pos descrip > > ACTATTTTTAGAGACCC NM_032685.1 307 hypothetical protein MGC13005 (MGC13005), > AGAAAAAAAAAAAAAAA NM_019110.2 27 hypothetical protein P1 p373c6 (P1P373C6) > NM_019110.2 27 hypothetical protein P1 p373c6 (P1P373C6) > AGCCACCACGCCTGGTC NM_003693.1 260 acetyl LDL receptor; SREC=scavenger > AGCCACCGCGCCCGGCC NM_007081.1 486 RAB, member of RAS oncogene family-like 2B > NM_013412.1 486 RAB, member of RAS oncogene family-like 2A > AGCCACCGCGCCTGGCC NM_000651.2 229 complement component (3b/4b) receptor 1, > NM_000573.2 229 complement component (3b/4b) receptor 1, > ATCAAAAAAAAAAAAAA NM_079421.1 25 cyclin-dependent kinase inhibitor 2D 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, t1.gb_id, t1.pos, t1.descrip FROM (SELECT *, nextval('dna_rownum1') AS idx FROM dna ORDER BY idx ) AS t1, (SELECT t2.tag, MIN(t2.idx) AS idx FROM (SELECT tag, nextval('dna_rownum2') AS idx FROM dna ORDER BY idx) AS t2 GROUP BY t2.tag ) AS t3 WHERE t1.tag = t3.tag ORDER BY t1.tag, t1.idx; -- (3) Note: (1) and (2) need to be executed at the same time, but (3) doesn't. And CREATE TEMP SEQUENCE is practicable in 7.2 or later. Regards, Masaru Sugawara