pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedunique fields
3+ messages / 3 participants
[nested] [flat]
* unique fields
@ 2010-09-23 03:30 Adrian Johnson <oriolebaltimore@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Adrian Johnson @ 2010-09-23 03:30 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: unique fields
@ 2010-09-23 07:33 Sergey Konoplev <gray.ru@gmail.com>
parent: Adrian Johnson <oriolebaltimore@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Sergey Konoplev @ 2010-09-23 07:33 UTC (permalink / raw)
To: Adrian Johnson <oriolebaltimore@gmail.com>; +Cc: pgsql-sql
Hi,
On 23 September 2010 07:30, Adrian Johnson <oriolebaltimore@gmail.com> wrote:
> I want to find out how many duplications are there for chr, cfrom and cto
Start with it http://www.postgresql.org/docs/9.0/interactive/tutorial-agg.html
p.s.
SELECT chr, cfrom, cto, count(*) FROM your_table GROUP BY 1, 2, 3;
>
> 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
>
--
Sergey Konoplev
Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: unique fields
@ 2010-09-23 10:01 Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
parent: Adrian Johnson <oriolebaltimore@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Oliveiros d'Azevedo Cristina @ 2010-09-23 10:01 UTC (permalink / raw)
To: Adrian Johnson <oriolebaltimore@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-09-23 10:01 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-09-23 03:30 unique fields Adrian Johnson <oriolebaltimore@gmail.com>
2010-09-23 07:33 ` Sergey Konoplev <gray.ru@gmail.com>
2010-09-23 10:01 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
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