SQL Problem Group by

From: Arun Goel <goel_ar1_at_hotmail.com>
Date: Sat, 30 Nov 2002 12:33:52 -0600
Message-ID: <asb0ai$p8f78$1@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 Sat Nov 30 2002 - 12:33:52 CST