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.