SQL group by with like condition

Question

suppose there is a SQL table: testTable with columns: clientID, colA, colB, colC.

reference   clientID    colA    colB    colC
---------------------------------------------
001            1        test1   test2   test3
002            1        test1   ball2   test3
003            2        test1   ball2   test3
004            2        test1   ball2   test3
005            3        test1   test2   test3
006            4        test1   test2   test3
007            4        test1   test2   test3
009            5        test1   ball2   test3
---------------------------------------------

i would like to select all the distinct rows where colB is like 'test' and group by the clientID. so i end up with:

reference    clientID    colA    colB    colC
----------------------------------------------
001             1        test1   test2   test3
005             3        test1   test2   test3
006             4        test1   test2   test3
----------------------------------------------

EDIT: reference column is unique if i use select distinct * .. from .. where colB like '%test%' group by clientID then the results returned does not have clientID grouped

Answer 1

When you group by one column, you will turn multiple rows into one, other columns in select has to be aggregate functions or subqueries. Which function to use depend on your need. Using MIN() like in example below will give you first result alphabetically if used with string column

SELECT clientID
    , MIN(colA) AS colA
    , MIN(colB) AS colB
    , MIN(colC) AS colC
FROM tableA
WHERE colB LIKE '%test%'
GROUP BY clientID

Edit: here is another solution, not using GROUP BY, but with Common Table Expression using ROW_NUMBER()

WITH CTE_Source AS 
(
  SELECT *
  FROM TableA
  WHERE colB LIKE '%test%'
) 
, CTE_Filter AS 
(
   SELECT *, ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY reference) RN
   FROM CTE_Source 
)
SELECT * 
FROM CTE_Filter 
WHERE RN = 1