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

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.

John Gage <jsmgage@numericable.fr> writes:
> 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;"

> 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 this, after inventing some table definitions, and got

ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 9: order by random() limit 3;
                 ^

You could probably make it work by putting the SELECT DISTINCT in a
sub-select with the ORDER BY & LIMIT outside.

> 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.

If your CGI infrastructure is so bad that you can't tell an error from
"no rows returned", let alone see the error message, then you really
really need a new CGI infrastructure.

            regards, tom lane