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