[SQL] a bit confused about distinct() function
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 Regards, Tena Sakai [email protected]
Re: [SQL] a bit confused about distinct() function
2009/3/29 Tena Sakai : > 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 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] a bit confused about distinct() function
"Tena Sakai" 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, allele2id from 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 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] a bit confused about distinct() function
Many thanks, Osvaldo. Regards, Tena Sakai -Original Message- From: Osvaldo Kussama [mailto:[email protected]] Sent: Sun 3/29/2009 10:44 AM To: Tena Sakai Cc: [email protected] Subject: Re: [SQL] a bit confused about distinct() function 2009/3/29 Tena Sakai : > 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
Re: [SQL] a bit confused about distinct() function
Many thanks, Tom. Regards, Tena Sakai [email protected] -Original Message- From: Tom Lane [mailto:[email protected]] Sent: Sun 3/29/2009 10:49 AM To: Tena Sakai Cc: [email protected] Subject: Re: [SQL] a bit confused about distinct() function "Tena Sakai" 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, allele2id from 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
