On Wednesday, November 5, 2003, at 12:16 AM, Jodi Kanter wrote:
Is there a straight forward way to pull out duplicates in a particular field given a value in another field?
For example, I have a table that lists users and study names associated with those users. Each user can have one or more study names.
If I understand you correctly, this is the table you're interested in. Your public.study table doesn't include any users as far as I can tell (though please correct me if I'm misunderstanding you).
��� ������������������������������ Table "public.study"
��� Column��� |����������� Type������������ |��������������� Modifiers����������������
--------------+-----------------------------+------------------------------------------
�sty_pk������ | integer�������������������� | not null default nextval('pk_seq'::text)
�study_name�� | character varying(128)����� | not null
�start_date�� | timestamp without time zone |
�sty_comments | text����������������������� |
�created_by�� | integer�������������������� |
Indexes: study_pkey primary key btree (sty_pk)
I think something like this is what you're looking for:
SELECT user, study_name, COUNT(*)
FROM <table linking user and study_name>
GROUP BY user, study_name
HAVING COUNT(*) > 1;
where the FROM clause lists the table linking users and study_names.
Does this help?
Michael
grzm myrealbox com
