Re: SQL Problem Group by

Hi Dieter,

I meant to say that
for same A,B I need the rows with maximum of same C. & also want to know which C.

I am able to find
A1 B1 3
A2 B2 3
using this query.
select A, B , MAX(Count_C ) as MAXC from (
SELECT Testtable.A, Testtable.B, Count(Testtable.C) AS Count_C FROM Testtable
GROUP BY Testtable.A, Testtable.B , Testtable.C ) GROUP BY A, B But I am mainly interested in what is the corresponding value of C for both rows.
So what I need as result is:
A1 B1 C1
A2 B2 C1

Count is optional.
I hope I am clear.

Thanks

--
----------------
Arun Goel
http://www.crml.uab.edu/~ag
"Dieter Valicek" <Dieter.Valicek_at_t-online.de> wrote in message
news:asb8p3$hpr$02$1_at_news.t-online.com...


> Hi Arun,

> what exactly do you mean with "for which there are maximum rows".

> I see only one row: A1 B1 C1  (Count = 3).

> Why is  A2 B2 C1 (Count = 2)  max?

>

>

> Regards

> Dieter

>

> "Arun Goel" <goel_ar1_at_hotmail.com> schrieb

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

> >

>

>