Обсуждение: a bit confused about distinct() function
<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>
2009/3/29 Tena Sakai <tsakai@gallo.ucsf.edu>: > 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 > Try: SELECT DISTINCT subjectid, markerid, allele1id, allele2id FROM tsakai.mygenotype2WHERE 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; Osvaldo
"Tena Sakai" <tsakai@gallo.ucsf.edu> writes:
> I am a bit confused about distinct() function.
Your confusion is in thinking that DISTINCT is a function. It is not;
it's just a keyword that modifies SELECT. Write
select distinct subjectid, markerid, allele1id, allele2idfrom tsakai.mygenotype2...
The other syntax was being read as an implicit row constructor, like
select distinct row(subjectid, markerid, allele1id, allele2id)
which actually will work in PG 8.4, but does not in existing releases;
and you likely wouldn't like the output format anyway, since it would
just be one composite column.
regards, tom lane
<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>
<p><font size="2">Many thanks, Tom.<br /><br /> Regards,<br /><br /> Tena Sakai<br /> tsakai@gallo.ucsf.edu<br /><br /><br/> -----Original Message-----<br /> From: Tom Lane [<a href="mailto:tgl@sss.pgh.pa.us">mailto:tgl@sss.pgh.pa.us</a>]<br/> Sent: Sun 3/29/2009 10:49 AM<br /> To: Tena Sakai<br />Cc: pgsql-sql@postgresql.org<br /> Subject: Re: [SQL] a bit confused about distinct() function<br /><br /> "Tena Sakai"<tsakai@gallo.ucsf.edu> writes:<br /> > I am a bit confused about distinct() function.<br /><br /> Your confusionis in thinking that DISTINCT is a function. It is not;<br /> it's just a keyword that modifies SELECT. Write<br/><br /> select distinct subjectid, markerid, allele1id, allele2id<br /> from tsakai.mygenotype2<br/> ...<br /><br /> The other syntax was being read as an implicit row constructor, like<br /><br/> select distinct row(subjectid, markerid, allele1id, allele2id)<br /><br /> which actually will work in PG8.4, but does not in existing releases;<br /> and you likely wouldn't like the output format anyway, since it would<br/> just be one composite column.<br /><br /> regards, tom lane<br /><br /></font>