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> 

pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: a bit confused about distinct() function
Next
From: "Tena Sakai"
Date:
Subject: Re: a bit confused about distinct() function