Re: a bit confused about distinct() function

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