SQL Problem

From: Arun Goel <goel_ar1_at_hotmail.com>
Date: Sat, 30 Nov 2002 15:31:47 -0600
Message-ID: <asbao5$obh3g$1_at_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

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

Thanks in advance
--



Arun Goel
http://www.crml.uab.edu/~ag Received on Sat Nov 30 2002 - 22:31:47 CET

Original text of this message