Re: SQL Problem Group by

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
>
Received on Sat Nov 30 2002 - 14:58:04 CST