DISTINCT not working...the way I want it to

From: John Gage <jsmgage(at)numericable(dot)fr>
To: NOVICE Postgres elist <pgsql-novice(at)postgresql(dot)org>
Subject: DISTINCT not working...the way I want it to
Date: 2010-06-16 19:02:44
Message-ID: 839D1711-D142-4310-B8BC-10FED50E3B03@numericable.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

In this fairly gruesome statement:

"insert into public.abstract_choices (correct_ans, apache_user,
descr_num, pmid, keyword, amer_term, abstr_lacking, abstr_having)

select 'false', '$apache_user', descr_num, pmid, keyword, amer_term,
abstr_lacking, abstr_having

from public.care_lesson_abstracts

where pmid not in (select pmid from public.abstract_choices where
apache_user = '$apache_user' and keyword = 'care')

order by random() limit 3;"

I sometimes get duplicates within the three items ("limit 3") selected.

Intuitively, I believed I could get rid of the possibility of
duplicates (there are 94 rows in the public.care_lesson_abstracts
table) by using "select distinct" in the second line:

"select distinct 'false', '$apache_user', descr_num, pmid, keyword,
amer_term, abstr_lacking, abstr_having"

This results in no rows being selected.

I tried "select distinct on (pmid)" where pmid is the primary key, but
that did not work either.

Is there a way to do this? I.e. what am I doing wrong?

Thanking you,

John

P.S. I don't have an error message because the statement is running
in a cgi script. I apologize for this, but I have a deadline
tomorrow, and I have not been able to psql it as yet.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2010-06-16 22:14:06 Re: DISTINCT not working...the way I want it to
Previous Message Chris Campbell 2010-06-16 15:58:54 Re: Mixed case text searches