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

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