SQL Server GROUP BY with condition

Question

In SQL server, I have this table:

PlanID  InfoID  Comp    CompName    GuidID  Object
629293  196672  42256   AAA         7       26
629294  196672  42256   AAA         7       24
629295  196672  10000   BBZ         7       21
629296  196673  09023   CCC         7       12
629297  196673  10001   BBY         7       14
629298  196674  09027   DDS         7       16
629299  196674  10004   BBH         1       12

And I want to group by InfoID (one row for each InfoID), choosing always the CompName != BBx (note: BBx is always listed under the CompName of my interest, no matter the alphabetical order or the Comp value):

PlanID  InfoID  Comp    CompName    GuidID  Object
629293  196672  42256   AAA         7       26
629296  196673  09023   CCC         7       14
629298  196674  09027   DDS         7       16

I used this code:

SELECT TOP (10000) MAX (PlanID) AS [PlanID]
  ,MAX (InfoID) AS [InfoID]
  ,MAX (Comp) AS [Comp] 
  ,MAX (CompName) AS [CompName]
  ,MAX (GuidID) AS [GuidID]
  ,MAX (Object) AS [Object]

  FROM [Prod].[dbo].[Panel]
  GROUP BY (InfoID)
  order by InfoID desc

which of course leads to:

PlanID  InfoID  Comp    CompName    GuidID  Object
629293  196672  42256   BBZ         7       26
629296  196673  10001   CCC         7       14
629298  196674  10004   DDS         7       16

What should I used instead of MAX (CompName)? Nice to have: also select the proper Comp linked to the 'not BBx' CompName.

Answer 1

Just add a WHERE clause to filter the CompName which you do not need to include in the grouping:

WHERE [CompName] NOT LIKE 'BB%'