Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Problem Group by

Re: SQL Problem Group by

From: Bert Bear <bertbear_at_NOSPAMbertbear.net>
Date: Sun, 01 Dec 2002 10:03:34 GMT
Message-ID: <W7lG9.924$_Q5.781110403@newssvr30.news.prodigy.com>


Arun,

I don't understand. You asked: How to filter out the rows with maximum occurances. By using the less than sign (<) get get all the rows except for the maximum occurance. As you can see in my original example, the having clause suppressses the maximum occurance row(s).

Using your example, you'd get when not using the having:

A1 B1 C1 3
A1 B1 C2 1
A2 B2 C1 2
A2 B2 C1 1

Using the having would give you:

A1 B1 C2 1
A2 B2 C1 2
A2 B2 C1 1

Isn't this what you wanted? If not, please explain in simplier (or more graphic terms). hmmm, suggestion: What output do you get if you use the less than (<) sign .vs. what output do you want? (how do they differ?)

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram

"Arun Goel" <goel_ar1_at_hotmail.com> wrote in message news:asb6ec$opneo$1_at_ID-127607.news.dfncis.de...
> Hi Bertram,
>
> But that does not serve my purpose.
> See If I have data like this
> A1 B1 C1
> A1 B1 C1
> A1 B1 C1
> A1 B1 C2
> A2 B2 C1
> A2 B2 C1
> A2 B2 C2
>
> *********I don't need this output*************
> A B C COUNT(*)
> -- -- -- ----------
> A1 B1 C1 3
>
> What I need is ..:
> A1 B1 C1
> A2 B2 C1.
>
> So group of A1 & B1 should be treated entirely separately.
>
> Thanks
> Arun
>
>
> --
> ----------------
> Arun Goel
> http://www.crml.uab.edu/~ag
> "Bert Bear" <bertbear_at_NOSPAMbertbear.net> wrote in message
> news:RY7G9.755$Sx4.646403270_at_newssvr30.news.prodigy.com...
> > Arun,
> >
> > In the having clause, change the equal sign (=) to a less than (<) sign.
> > I'm appending an example. You'll see in the example:
> >
> > 1) The first query/select returns 22 rows, while the second returns 21
> > rows.
> > 2) The row with the largest count is NOT in the results of the second
> > query/select.
> >
> > Bertram Moshier
> > Oracle Certified Professional 8i and 9i DBA
> >
> > http://www.bmoshier.net/bertram
> >
> > 12:53:57 By your command, human>
> > select lastdate, count(*)
> > from REMA
> > where status=1
> > group by lastdate;
> >
> > LASTDATE COUNT(*)
> > -------------------- ----------
> > 08-Nov-2002 00:00:00 1409
> > 09-Nov-2002 00:00:00 13
> > 10-Nov-2002 00:00:00 5
> > 11-Nov-2002 00:00:00 29
> > 12-Nov-2002 00:00:00 17
> > 13-Nov-2002 00:00:00 15
> > 14-Nov-2002 00:00:00 8
> > 15-Nov-2002 00:00:00 35
> > 16-Nov-2002 00:00:00 15
> > 17-Nov-2002 00:00:00 15
> > 18-Nov-2002 00:00:00 25
> > 19-Nov-2002 00:00:00 32
> > 20-Nov-2002 00:00:00 28
> > 21-Nov-2002 00:00:00 26
> > 22-Nov-2002 00:00:00 27
> > 23-Nov-2002 00:00:00 24
> > 24-Nov-2002 00:00:00 1
> > 25-Nov-2002 00:00:00 15
> > 26-Nov-2002 00:00:00 16
> > 27-Nov-2002 00:00:00 14
> > 28-Nov-2002 00:00:00 18
> > 29-Nov-2002 00:00:00 3
> >
> > 22 rows selected.
> >
> > Elapsed: 00:00:00.00
> > 12:55:29 By your command, human>
> > select lastdate, count(*)
> > from REMA
> > where status=1
> > group by lastdate
> > having(count(*))<(select max(count(*))
> > from REMA
> > where status=1
> > group by lastdate);
> >
> > LASTDATE COUNT(*)
> > -------------------- ----------
> > 09-Nov-2002 00:00:00 13
> > 10-Nov-2002 00:00:00 5
> > 11-Nov-2002 00:00:00 29
> > 12-Nov-2002 00:00:00 17
> > 13-Nov-2002 00:00:00 15
> > 14-Nov-2002 00:00:00 8
> > 15-Nov-2002 00:00:00 35
> > 16-Nov-2002 00:00:00 15
> > 17-Nov-2002 00:00:00 15
> > 18-Nov-2002 00:00:00 25
> > 19-Nov-2002 00:00:00 32
> > 20-Nov-2002 00:00:00 28
> > 21-Nov-2002 00:00:00 26
> > 22-Nov-2002 00:00:00 27
> > 23-Nov-2002 00:00:00 24
> > 24-Nov-2002 00:00:00 1
> > 25-Nov-2002 00:00:00 15
> > 26-Nov-2002 00:00:00 16
> > 27-Nov-2002 00:00:00 14
> > 28-Nov-2002 00:00:00 18
> > 29-Nov-2002 00:00:00 3
> >
> > 21 rows selected.
> >
> > Elapsed: 00:00:00.00
> >
> >
> >
> > "Arun Goel" <goel_ar1_at_hotmail.com> wrote in message
> > news:asb0ai$p8f78$1_at_ID-127607.news.dfncis.de...
> > > Hi all,
> > >
> > > I am facing problem in getting the desired result.
> > >
> > > Here is my problem..
> > > my table look like
> > > ----->temp(A,B,C);
> > > Data is as follows:
> > > A B C
> > > -- -- --
> > > A1 B1 C1
> > > A1 B1 C1
> > > A1 B1 C1
> > > A1 B1 C2
> > > A2 B2 C1
> > > A2 B2 C1
> > > A2 B2 C2
> > >
> > > I need is the value of A, B,C for which there are maximum rows.
> > > It means I just need is A1,B1,C1 & A2,B2,C1 only.
> > >
> > > I tried this query
> > > select A,B,C,count(*)
> > > from temp
> > > group by A,B,C;
> > >
> > > It gives me this as expected
> > > A B C COUNT(*)
> > > -- -- -- ----------
> > > A1 B1 C1 3
> > > A1 B1 C2 1
> > > A2 B2 C1 2
> > > A2 B2 C2 1
> > >
> > > I don't how to filter out the rows with maximum occurences.
> > >
> > > When I use "Having" in group as follows:
> > >
> > > select A,B,C,count(*)
> > > 2 from temp
> > > 3 group by A,B,C
> > > 4 Having(count(*))=(
> > > 5 select max(count(*))
> > > 6 from temp
> > > 7 group by A,B,C
> > > 8 );
> > > A B C COUNT(*)
> > > -- -- -- ----------
> > > A1 B1 C1 3
> > >
> > >
> > > Then I don't get the A2,B2,C1 at all as evident from the result.
> > >
> > > Can anyone suggest me how can I get thje desired tuples.
> > >
> > > Regards
> > > --
> > > ----------------
> > > Arun Goel
> > > http://www.crml.uab.edu/~ag
> > >
> > >
> > >
> >
> >
>
>
Received on Sun Dec 01 2002 - 04:03:34 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US