CODEWITHSUNDEEP
sqlsql-servergroup-bywhere-clausesql-like
yigames
yigames

Reputation: 185

SQL group by with like condition

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

Upvotes: 0

Views: 3905

Answers (6)

JayaPrakash
JayaPrakash

Reputation: 199

Try this,

 Select distinct * From tableA where colB like '%test%'

Upvotes: 0

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

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

Upvotes: 5

ahmed abdelqader
ahmed abdelqader

Reputation: 3558

Simply use:-

group by  clientID,colA,colB,colC

instead of 

group by  clientID

Demo:-

Create table testTable (clientID int , colA varchar(10), colB varchar(10), colC varchar(10))
go
insert into testTable values
(1,'test1','test2','test3'),
(1,'test1','ball2','test3'),
(2,'test1','ball2','test3'),
(2,'test1','ball2','test3'),
(3,'test1','test2','test3'),
(4,'test1','test2','test3'),
(4,'test1','test2','test3'),
(5,'test1','ball2','test3')


select * from testTable
where colB LIKE '%test%'
group by  clientID,colA,colB,colC

Output:-

clientID    colA    colB    colC
  1        test1    test2   test3
  3        test1    test2   test3
  4        test1    test2   test3

Upvotes: 0

etsa
etsa

Reputation: 5060

When you use GROUP BY you have to specify all columns that you use in the query and that don't use an aggregation function. So, for example,

SELECT CLIENTID, COLA, COLB, COLC, COUNT(*) AS RC
FROM YOURTABLE
WHERE COLB LIKE '%test%'
GROUP BY CLIENTID, COLA, COLB, COLC

Upvotes: 0

nikunj sobhashana
nikunj sobhashana

Reputation: 181

The where clause goes before the having and the group by. If you want to filter out records before the grouping the condition goes in the where clause, and if you want to filter out grouped records the condition goes in the having clause:

select ...
from ...
where ...
group by ...
having ...

or something like this one if above thing is not worked 

select ...
from (
   select ...
   from ...
   where ...
   group by ...
   having ...
) x
where ...

Upvotes: 0

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

Just a distinct is not suffice?

Select distinct * from #data where ColB like 'test%'

Or you can use top 1 with ties if you require only certain columns to consider on distinct

Select top (1) with ties * from #groupby where ColB like 'test%'
order by row_number() over(partition by clientid order by cola,colb,colc)--you can include your required columns only

Upvotes: 1

Related Questions