Re: Counting duplicates in SQL

From: Ed Prochak <edprochak_at_gmail.com>
Date: 5 Sep 2006 10:22:07 -0700
Message-ID: <1157476926.963266.100260@m73g2000cwd.googlegroups.com>

Jack wrote:
> "DA Morgan" <damorgan_at_psoug.org> wrote in message
> news:1157335769.824274_at_bubbleator.drizzle.com...
> > Jack wrote:
> >> "Robert Klemme" <shortcutter_at_googlemail.com> wrote in message
> >> news:4lttnpF3ffagU1_at_individual.net...
> >>> Jack wrote:
> >>>> Hi
> >>>>
> >>>> I have an input table which can have multiple duplicate rows - same
> >>>> name
> >>>> field different ID's.
> >>>> I can return the duplicate values using the SQL below, but would also
> >>>> like
> >>>> the number of rows containing each duplicate value
> >>>>
> >>>> I have:
> >>>>
> >>>> SELECT Product_ID, Product_Name,
> >>>> (SELECT MIN(Product_ID) FROM Products AS P1
> >>>> WHERE Products.Product_Name = P1.Product_Name) AS OldID
> >>>> FROM Products WHERE Product_ID NOT IN
> >>>> (SELECT MIN(Product_ID) FROM Products AS P2
> >>>> WHERE Products.Product_Name = P2.Product_Name)
> >>>>
> >>>> 85157 Chips 84050
> >>>> 82103 Fish 76929
> >>>> 82103 Fish 76929
> >>>> 99999 Sausage 82785
> >>>> 83780 Sausage 82785
> >>>>
> >>>>
> >>>> So for example for "Sausage" I would like to report how many rows have
> >>>> an
> >>>> ID
> >>>> of 82785, 83780 and 99999 respectively. I can't see how I can adapt my
> >>>> statement above. Is there another approach using just SQL?
> >>> Hm, this lookes like homework to me. What else did you try?
> >>
> >> Hm, looks like you entered the "I'm a patronising twat" competition to
> >> me.
> >
> > Jack as an instructor at the University of Washington teaching Oracle I
> > am grateful for people who provide hints but not solutions and it is
> > wrong for you to denigrate them.

>

> I don't care where he teaches.
> It is wrong to denigate someone who asks a legitimate question as a
> possible/probable *CHEAT*

How in the world did you read Robert's post and get any implications about cheating???
He simply prompted "what else did you try?"

>

> > It is impossible for instructors to monitor all of the ways that
> > students can cheat and self-policing professions are of great value.

>

> So?!!?!?!??!!?
> What was he trying to accomplish by inferring I was a cheat?
> The only possible answer is that he would put others off from helping.
>
> That is what pissed me off.

Sure it wasn't a guilty conscience?? 8^)

just relax.

> ********************

>

> *self policing professionals" lol. Vigilanties in the Oracle newsgroups
> police state more like.

>


> > I am sure you wouldn't want to find yourself working on a team with

> > someone that couldn't pull their weight. Please reconsider your
> > attitude.

>

> My attitude is fine. If someone suspects that another is a *cheat* then
> don't post any help.

Okay, I won't.

>

> If their institutions grade by continual assesment rather than final exam
> that is their problem. If it is easy for their students to cheat then fix
> it. Don't stigmatise others in the *real* world rather than be anal about
> their own institutions.

>

> There is no need to stigmatise. It is counterproductive. Remember McCarthy?
> Don't you Americans ever learn?

You are severely overreacting. Take a deep breath. Count to 10.
Then wait a day before your reply.
It will all look better in the morning.

  Ed. Received on Tue Sep 05 2006 - 12:22:07 CDT