SQL grouping

I have a table with the following columns:

A   B   C
---------
1   10  X
1   11  X
2   15  X
3   20  Y
4   15  Y
4   20  Y

I want to group the data based on the B and C columns and count the distinct values of the A column. But if there are two ore more rows where the value on the A column is the same I want to get the maximum value from the B column.

If I do a simple group by the result would be:

B   C    Count
--------------
10  X    1
11  X    1
15  X    1
20  Y    2
15  Y    1

What I want is this result:

B   C    Count
--------------
11  X    1
15  X    1
20  Y    2

Is there any query that can return this result. Server is SQL Server 2005.

From stackoverflow

• Check this out. This should work in Oracle, altough havent tested it; select count(a), BB, CC from ( select a, max(B) BB, Max(C) CC from yourtable group by a ) group by BB,CC

• I like to work in steps: first get rid of duplicate A records, then group. Not the most efficient, but it works on your example.

  with t1 as (
      select A, max(B) as B, C 
       from YourTable
       group by A, C
  )
  select count(A) as CountA, B, C
      from t1
      group by B, C
  

  Albert : yes, that works :) Was trying to hard to use a single query...and forgot other alternatives.

• I have actually tested this:

  SELECT 
      MAX( B ) AS B,
      C,
      Count 
  FROM
  (
      SELECT
          B, C, COUNT(DISTINCT A) AS Count
      FROM
          t
      GROUP BY
      B, C
  ) X
  GROUP BY C, Count
  

  and it gives me:

   B     C     Count    
   ----  ----  -------- 
   15    X     1        
   15    y     1        
   20    y     2
  

• WITH cteA AS 
  (
   SELECT 
    A, C, 
    MAX(B) OVER(PARTITION BY A, C) [Max]
   FROM T1
  )
  
  SELECT 
   [Max] AS B, C, 
   COUNT(DISTINCT A) AS [Count]
  FROM cteA
  GROUP BY C, [Max];