Re: unique fields

Howdy, Adrian

Dunno if this is exactly what you want


SELECT *
FROM
(
SELECT chr,cfrom,cto,count(*) as numberOfDuplicates
FROM t_fairly_large_table 
GROUP BY chr,cfrom,cto
) x
NATURAL JOIN t_fairly_large_table y
WHERE numberOfDuplicates > 1

The idea of this (untested) query
is to produce something like

chr| cfrom | cto | numberOfDuplicates| sample_id
c2    19       20       3                              1
c2    19       20       3                              2
c2    19       20       3                              3
c5    10        11      2                              1
c5    10        11      2                              3


Can this be what you need?

Best,
Oliver

----- Original Message ----- 
From: "Adrian Johnson" <oriolebaltimore@gmail.com>
To: <pgsql-sql@postgresql.org>
Sent: Thursday, September 23, 2010 4:30 AM
Subject: [SQL] unique fields


> hi:
> 
> I have a fairly large table.
> 
> sample_id | chr | cfrom | cto |
> -------------------------------------------
> 1                c2    19       20
> 2                c2    19       20
> 3                c2    19       20
> 1                c5    10       11
> 3                c5    10       11
> 
> 
> (25,000 rows)
> 
> I want to find out how many duplications are there for chr, cfrom and cto
> 
> a.   c2,19,20 are common to samples 1,2 and 3.
> 
> since there will be many instances like that, do I have to loop over
> entire rows and find common chr, cfrom and c2 and ouput with
> sample_id.
> how can I do that.
> 
> thanks
> adrian
> 
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql