Re: SQL Problem Group by

From: Chan Sanghera <ChanSaghera_at_tarans.net>
Date: 3 Dec 2002 05:45:43 -0800
Message-ID: <6a566d3d.0212030545.6a44098b@posting.google.com>

Hi Arun

you could try the following looks worse than it is.....

select t1.a, t1.b, t1.c, t1.d
from ( select a,b,c,count(*) d

       from google2
       group by a, b, c
      ) t1,
     ( SELECT A,B, MAX(D) d
       FROM ( select a,b,C, count(*) d
              from google2
              group by a, b, C
            )
       group by a, b
      ) t2

 where t1.a = t2.a
 and t1.b = t2.b
 and t1.d = t2.d

regards
Chan.

"Damjan S. Vujnovic" <damjan_at_NOSPAMgaleb.etf.bg.ac.yu> wrote in message news:<asbda9$qgo$1_at_news.etf.bg.ac.yu>...
> "Arun Goel" wrote:
>
> : 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
>
> How about (you can omit a view by nesting a query):
>
> create view xxx(A, B, C, D) as
> select A, B, C, count(*)
> from temp
> group by A, B, C;
>
> select x1.A, x1.B, x1.C, x1.D
> from xxx x1
> where x1.D = (
> select max(x2.D)
> from xxx x2
> where x2.a = x1.a and x2.b = x1.b);
>
> :
> : 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
>
>
> --
> regards,
> Damjan S. Vujnovic
>
> University of Belgrade
> School of Electrical Engineering
> Department of Computer Engineering & Informatics
> Belgrade, Yugoslavia
>
> http://galeb.etf.bg.ac.yu/~damjan/
Received on Tue Dec 03 2002 - 07:45:43 CST