pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Masaru Sugawara <rk73@sea.plala.or.jp>
To: mccorkle@bnl.gov
Cc: pgsql-sql@postgresql.org
Subject: Re: blanking out repeated columns in rows
Date: Thu, 09 May 2002 01:51:59 +0900
Message-ID: <20020509005131.4FF4.RK73@sea.plala.or.jp> (raw)
In-Reply-To: <20020507150350.GA24648@avenger.bio.bnl.gov>
References: <20020507150350.GA24648@avenger.bio.bnl.gov>

On Tue, 7 May 2002 11:03:50 -0400
Sean McCorkle <mccorkle@avenger.bio.bnl.gov> 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





view thread (3+ messages)  latest in thread

Message-ID: <20020509005131.4FF4.RK73@sea.plala.or.jp>
Permalink:  ../20020509005131.4FF4.RK73@sea.plala.or.jp/
Also on:    postgresql.org/message-id/20020509005131.4FF4.RK73@sea.plala.or.jp

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: rk73@sea.plala.or.jp, mccorkle@bnl.gov
  Subject: Re: blanking out repeated columns in rows
  In-Reply-To: <20020509005131.4FF4.RK73@sea.plala.or.jp>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox