From: | "Tena Sakai" <tsakai(at)gallo(dot)ucsf(dot)edu> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | a bit confused about distinct() function |
Date: | 2009-03-29 17:27:27 |
Message-ID: | FE44E0D7EAD2ED4BB2165071DB8E328C04E84A50@egcrc-ex01.egcrc.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi Everybody,
I am a bit confused about distinct() function.
I wrote a simple query like this:
select subjectid, markerid, allele1id, allele2id
from tsakai.mygenotype2
where subjectid in (53684, 53688, 53699, 53700, 53704, 53705, 53713, 53714, 53716, 53724)
and
markerid in (1259501, 1259504, 1260210, 1260211, 1260212, 1260214, 1260215, 1260238, 1260248, 1260562)
order
by subjectid;
Here's what I got back:
subjectid | markerid | allele1id | allele2id
-----------+----------+-----------+-----------
53684 | 1260214 | 2521543 | 2521543
53684 | 1260214 | 2521543 | 2521543
53684 | 1260215 | 2521537 | 2521538
53688 | 1260562 | 2522243 | 2522243
53688 | 1260562 | 2522243 | 2522243
53699 | 1260562 | 2522243 | 2522243
53699 | 1260214 | 2521543 | 2521544
53699 | 1260214 | 2521543 | 2521544
53704 | 1260215 | 2521537 | 2521537
53714 | 1260214 | 2521543 | 2521543
(10 rows)
Which is good, but seeing the duplicate rows in result
made me want to write:
select distinct (subjectid, markerid, allele1id, allele2id)
from tsakai.mygenotype2
where subjectid in (53684, 53688, 53699, 53700, 53704, 53705, 53713, 53714, 53716, 53724)
and
markerid in (1259501, 1259504, 1260210, 1260211, 1260212, 1260214, 1260215, 1260238, 1260248, 1260562)
order
by subjectid;
and what I got back was:
ERROR: could not identify an ordering operator for type record
HINT: Use an explicit ordering operator or modify the query.
Could somebody give me a tip as to what I could do
to get what I want? Ie., I want get back is:
subjectid | markerid | allele1id | allele2id
-----------+----------+-----------+-----------
53684 | 1260214 | 2521543 | 2521543
53684 | 1260215 | 2521537 | 2521538
53688 | 1260562 | 2522243 | 2522243
53699 | 1260562 | 2522243 | 2522243
53699 | 1260214 | 2521543 | 2521544
53704 | 1260215 | 2521537 | 2521537
53714 | 1260214 | 2521543 | 2521543
Regards,
Tena Sakai
tsakai(at)gallo(dot)ucsf(dot)edu
From | Date | Subject | |
---|---|---|---|
Next Message | Osvaldo Kussama | 2009-03-29 17:44:28 | Re: a bit confused about distinct() function |
Previous Message | Jasen Betts | 2009-03-28 11:57:00 | Re: Table to Excel |