Re: SQL Problem Group by

From: Dieter Valicek <Dieter.Valicek_at_t-online.de>
Date: Sat, 30 Nov 2002 23:59:08 +0100
Message-ID: <asbfrv$t5h$00$1@news.t-online.com>

"Arun Goel" <goel_ar1_at_hotmail.com> schrieb :
> Hi Dieter,
>
> I meant to say that
> 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
>
> Count is optional.
> I hope I am clear.
>
> Thanks
> --
> ----------------
> Arun Goel
> http://www.crml.uab.edu/~ag

Hi Arun,
 but what is the result from:
A B C

• -- -- A1 B1 C1 A1 B1 C1 A1 B1 C2 A1 B1 C2

I have no idea how to do that with a simple Select.

But you can try something like that (not testet): (assuming A, B is int)

Declare @LastA int, @LastB int
Set @LastA = 0
Set @LastB = 0
DECLARE TestC CURSOR FOR
  select A,B,C,count(*) D

     from temp
     group by A,B,C

  ORDER BY A, B, D OPEN TestC
FETCH NEXT FROM TestC
WHILE @@FETCH_STATUS = 0
BEGIN
  IF A <> LastA OR B <> LastB
  BEGIN

    Set @LastA = A WHERE CURRENT OF TestC
    Set @LastB = B WHERE CURRENT OF TestC
    Set @LastB = C WHERE CURRENT OF TestC

    PRINT @LastA,@LastB,@LastC
  END
  FETCH NEXT FROM TestC
END CLOSE TestC
DEALLOCATE TestC

Regards
Dieter Received on Sat Nov 30 2002 - 16:59:08 CST