pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Sean McCorkle <mccorkle@avenger.bio.bnl.gov>
To: pgsql-sql@postgresql.org
Subject: blanking out repeated columns in rows
Date: Tue, 7 May 2002 11:03:50 -0400
Message-ID: <20020507150350.GA24648@avenger.bio.bnl.gov> (raw)


Here's one for all you SQL jockeys out there (hope this is a good group
to post this query)

I'm currently (happily) using PostgreSQL for doing DNA expression analysis,
and I often end up with joins where columns on the left side get repeated
to matching differing 

For example, a select output ends up looking like this:
(from psql.  i've removed the - and | lines)

    tag             gb_id      pos     descrip

ACTATTTTTAGAGACCC NM_032685.1  307 hypothetical protein MGC13005 (MGC13005),
AGAAAAAAAAAAAAAAA NM_019110.2   27 hypothetical protein P1 p373c6 (P1P373C6)
AGAAAAAAAAAAAAAAA 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
AGCCACCGCGCCCGGCC NM_013412.1  486 RAB, member of RAS oncogene family-like 2A
AGCCACCGCGCCTGGCC NM_000651.2  229 complement component (3b/4b) receptor 1,
AGCCACCGCGCCTGGCC NM_000573.2  229 complement component (3b/4b) receptor 1,
ATCAAAAAAAAAAAAAA NM_079421.1   25 cyclin-dependent kinase inhibitor 2D


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


Is there any slick way to accomplish this in SQL?  Or is there a switch
or something I've missed in postgresql?  Currently I'm postprocessing
the output with a perl script, but I'd prefer the SQL to generate
it directly, if possible.

Thanks, folks!
=sean

-------------------------------------------------------------------------------
Sean R. McCorkle           mccorkle@bnl.gov   | Haven't you heard, that aside
Genome Group               (631) 344-4270 ph  | from the details, there's
Biology Department         (631) 344-3407 fax | nothing further to be found?
Brookhaven National Laboratory                |    --Master Dogen's 300 Koan
Upton, New York  11973                        |      Shobogenzo (commentary)



view thread (3+ messages)  latest in thread

Message-ID: <20020507150350.GA24648@avenger.bio.bnl.gov>
Permalink:  ../20020507150350.GA24648@avenger.bio.bnl.gov/
Also on:    postgresql.org/message-id/20020507150350.GA24648@avenger.bio.bnl.gov

 · 

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: mccorkle@avenger.bio.bnl.gov
  Subject: Re: blanking out repeated columns in rows
  In-Reply-To: <20020507150350.GA24648@avenger.bio.bnl.gov>

* 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