a bit confused about distinct() function
| От | Tena Sakai |
|---|---|
| Тема | a bit confused about distinct() function |
| Дата | |
| Msg-id | FE44E0D7EAD2ED4BB2165071DB8E328C04E84A50@egcrc-ex01.egcrc.org обсуждение исходный текст |
| Ответы |
Re: a bit confused about distinct() function
(Osvaldo Kussama <osvaldo.kussama@gmail.com>)
Re: a bit confused about distinct() function (Tom Lane <tgl@sss.pgh.pa.us>) |
| Список | pgsql-sql |
<p><font size="2">Hi Everybody,<br /><br /> I am a bit confused about distinct() function.<br /><br /> I wrote a simple querylike this:<br /><br /> select subjectid, markerid, allele1id, allele2id<br /> from tsakai.mygenotype2<br /> wheresubjectid in (53684, 53688, 53699, 53700, 53704, 53705, 53713, 53714, 53716, 53724)<br /> and<br /> markeridin (1259501, 1259504, 1260210, 1260211, 1260212, 1260214, 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 /> (10rows)<br /><br /> Which is good, but seeing the duplicate rows in result<br /> made me want to write:<br /><br /> selectdistinct (subjectid, markerid, allele1id, allele2id)<br /> from tsakai.mygenotype2<br /> where subjectid in (53684,53688, 53699, 53700, 53704, 53705, 53713, 53714, 53716, 53724)<br /> and<br /> markerid in (1259501,1259504, 1260210, 1260211, 1260212, 1260214, 1260215, 1260238, 1260248, 1260562)<br /> order<br /> by subjectid;<br/><br /> and what I got back was:<br /> ERROR: could not identify an ordering operator for type record<br/> HINT: Use an explicit ordering operator or modify the query.<br /><br /> Could somebody give me a tip as towhat 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 /> Regards,<br /><br /> Tena Sakai<br />tsakai@gallo.ucsf.edu<br /></font>
В списке pgsql-sql по дате отправления: