Re: a bit confused about distinct() function - Mailing list pgsql-sql
| From | Tena Sakai |
|---|---|
| Subject | Re: a bit confused about distinct() function |
| Date | |
| Msg-id | FE44E0D7EAD2ED4BB2165071DB8E328C04E84A52@egcrc-ex01.egcrc.org Whole thread Raw |
| In response to | a bit confused about distinct() function ("Tena Sakai" <tsakai@gallo.ucsf.edu>) |
| List | pgsql-sql |
<p><font size="2">Many thanks, Osvaldo.<br /><br /> Regards,<br /><br /> Tena Sakai<br /><br /><br /> -----Original Message-----<br/> From: Osvaldo Kussama [<a href="mailto:osvaldo.kussama@gmail.com">mailto:osvaldo.kussama@gmail.com</a>]<br/> Sent: Sun 3/29/2009 10:44 AM<br /> To:Tena Sakai<br /> Cc: pgsql-sql@postgresql.org<br /> Subject: Re: [SQL] a bit confused about distinct() function<br /><br/> 2009/3/29 Tena Sakai <tsakai@gallo.ucsf.edu>:<br /> > Hi Everybody,<br /> ><br /> > I am a bit confusedabout distinct() function.<br /> ><br /> > I wrote a simple query like this:<br /> ><br /> > selectsubjectid, markerid, allele1id, allele2id<br /> > from tsakai.mygenotype2<br /> > where subjectid in (53684,53688, 53699, 53700, 53704, 53705, 53713, 53714,<br /> > 53716, 53724)<br /> > and<br /> > markerid in (1259501, 1259504, 1260210, 1260211, 1260212, 1260214,<br /> > 1260215, 1260238, 1260248, 1260562)<br/> > order<br /> > by subjectid;<br /> ><br /> > Here's what I got back:<br /> ><br /> > subjectid | markerid | allele1id | allele2id<br /> > -----------+----------+-----------+-----------<br /> > 53684 | 1260214 | 2521543 | 2521543<br /> > 53684 | 1260214 | 2521543 | 2521543<br /> > 53684 | 1260215 | 2521537 | 2521538<br /> > 53688 | 1260562 | 2522243 | 2522243<br /> > 53688 | 1260562 | 2522243 | 2522243<br /> > 53699 | 1260562 | 2522243 | 2522243<br /> > 53699 | 1260214 | 2521543 | 2521544<br /> > 53699 | 1260214 | 2521543 | 2521544<br /> > 53704 | 1260215 | 2521537 | 2521537<br /> > 53714 | 1260214 | 2521543 | 2521543<br /> > (10 rows)<br/> ><br /> > Which is good, but seeing the duplicate rows in result<br /> > made me want to write:<br />><br /> > select distinct (subjectid, markerid, allele1id, allele2id)<br /> > from tsakai.mygenotype2<br />> where subjectid in (53684, 53688, 53699, 53700, 53704, 53705, 53713,<br /> > 53714, 53716, 53724)<br /> > and<br /> > markerid in (1259501, 1259504, 1260210, 1260211, 1260212, 1260214,<br /> > 1260215,1260238, 1260248, 1260562)<br /> > order<br /> > by subjectid;<br /> ><br /> > and what I gotback was:<br /> > ERROR: could not identify an ordering operator for type record<br /> > HINT: Use an explicitordering operator or modify the query.<br /> ><br /> > Could somebody give me a tip as to what I could do<br/> > to get what I want? Ie., I want get back is:<br /> ><br /> > subjectid | markerid | allele1id | allele2id<br/> > -----------+----------+-----------+-----------<br /> > 53684 | 1260214 | 2521543 | 2521543<br/> > 53684 | 1260215 | 2521537 | 2521538<br /> > 53688 | 1260562 | 2522243 | 2522243<br/> > 53699 | 1260562 | 2522243 | 2522243<br /> > 53699 | 1260214 | 2521543 | 2521544<br/> > 53704 | 1260215 | 2521537 | 2521537<br /> > 53714 | 1260214 | 2521543 | 2521543<br/> ><br /><br /><br /> Try:<br /> SELECT DISTINCT subjectid, markerid, allele1id, allele2id<br /> FROM tsakai.mygenotype2<br/> WHERE subjectid IN (53684, 53688, 53699, 53700, 53704, 53705, 53713,<br /> 53714, 53716, 53724)<br/> AND markerid IN (1259501, 1259504, 1260210, 1260211, 1260212,<br /> 1260214, 1260215, 1260238, 1260248, 1260562)<br/> ORDER BY subjectid;<br /><br /> Osvaldo<br /><br /></font>