Re: SQL Problem Group by

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