Postgres Pro
Downloads
Home   >   mailing lists

Thread: query assistance

query assistance

From
Jodi Kanter
Date:
Is there a straight forward way to pull out duplicates in a particular field given a value in another field?<br /> For
example,I have a table that lists users and study names associated with those users. Each user can have one or more
studynames. My goal is to determine if any of these people have duplicate study names. There are duplicated study names
acrossthe system and that is ok. I just want to see if any users have duplicate study names among their studies.<br />
Mytable looks like this:<br /><br />                                    Table "public.study"<br />     Column   
|           Type             |                Modifiers                 <br />
--------------+-----------------------------+------------------------------------------<br/>  sty_pk       |
integer                    | not null default nextval('pk_seq'::text)<br />  study_name   | character varying(128)     
|not null<br />  start_date   | timestamp without time zone | <br />  sty_comments | text                        | <br
/> created_by   | integer                     | <br /> Indexes: study_pkey primary key btree (sty_pk)<br /><br /> I am
concerenedwith study_name. The created_by field tells me who owns it.<br /> Thanks<br /> Jodi<br /><br /><div
class="moz-signature">--<br /></div><div class="Section1"><p class="MsoNormal"><i><span style="font-size: 9pt;
font-family:Arial;">_______________________________<br /></span></i><i><span style="font-size: 10pt;">Jodi L Kanter<br
/>BioInformatics Database Administrator<br /> University of Virginia<br /> (434) 924-2846<br /><a
href="mailto:jkanter@virginia.edu">jkanter@virginia.edu</a></span></i><spanstyle="font-size: 11pt; font-family:
Arial;"><brstyle="" /><br style="" /></span><p class="MsoNormal"><span style="font-size: 11pt; font-family:
Arial;"> </span><pclass="MsoNormal"><i><span style="font-size: 9pt; font-family: Arial;"> </span></i><p
class="MsoNormal"><i><spanstyle="font-size: 9pt; font-family: Arial;"> </span></i></div>

Re: query assistance

From
Richard Huxton
Date:
On Tuesday 04 November 2003 15:16, 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. My goal is
> to determine if any of these people have duplicate study names. There
> are duplicated study names across the system and that is ok. I just want
> to see if any users have duplicate study names among their studies.

Something like:

SELECT created_by, study_name, count(sty_pk)
FROM study
GROUP BY created_by, study_name
HAVING count(sty_pk) > 1;

--  Richard Huxton Archonet Ltd

Re: query assistance

From
Michael Glaesemann
Date:
Hi Jodi,

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=20=
=20
> field given a value in another field?
> For example, I have a table that lists users and study names=20=20
> associated with those users. Each user can have one or more study=20=20
> names.

If I understand you correctly, this is the table you're interested in.=20=
=20
Your public.study table doesn't include any users as far as I can tell=20=
=20
(though please correct me if I'm misunderstanding you).

> =A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0 Table "public.study"
> =A0=A0=A0 Column=A0=A0=A0 |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Type=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=20=20
> Modifiers=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0
> --------------+-----------------------------=20
> +------------------------------------------
> =A0sty_pk=A0=A0=A0=A0=A0=A0 | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0 | not null default=20=20
> nextval('pk_seq'::text)
> =A0study_name=A0=A0 | character varying(128)=A0=A0=A0=A0=A0 | not null
> =A0start_date=A0=A0 | timestamp without time zone |
> =A0sty_comments | text=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0 |
> =A0created_by=A0=A0 | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0 |
> 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