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(at)gmail(dot)com>
To: <pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql