Re: a bit confused about distinct() function

Many thanks, Osvaldo.

Regards,

Tena Sakai

-----Original Message-----
From: Osvaldo Kussama [mailto:osvaldo(dot)kussama(at)gmail(dot)com]
Sent: Sun 3/29/2009 10:44 AM
To: Tena Sakai
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] a bit confused about distinct() function

2009/3/29 Tena Sakai <tsakai(at)gallo(dot)ucsf(dot)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.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;

Osvaldo