T-SQL Group by with a where clause

Question

 Masterid    CC  CLA DES NLCLA   NLDES
 -------------------------------------
 53006141    CN  0   0   1       1
 53006141    US  1   1   1       1
 53006141    UK  1   1   0       0
 53006142    US  1   1   0       0
 53006142    UK  1   1   0       0
 53006143    CN  0   0   1       1
 53006143    US  1   1   0       0
 53006143    UK  1   1   0       0

From the above data I need to produce

• a list of MasterIds where there is CC = US or CC = CN and NLCLA = 1 and NLDES = 1

The output should be

53006141
53006143

There has to be both CN and US under a MasterID.

Can someone help me to do this in SQL please?

Solution 1

You can do this by adding a WHERE clause which will return rows with either US or CN:

select distinct Masterid
from yourtable
where cc in ('US', 'CN')
  and NLCLA = 1
  AND NLDES = 1

See SQL Fiddle with Demo

If you want the result to include both the CN and US, then you can use:

select Masterid
from yourtable
where cc in ('US', 'CN')
  and NLCLA = 1
  AND NLDES = 1
group by masterid
having count(distinct cc) = 2

See SQL Fiddle with Demo.

Another way that this could be done is using an EXISTS to get the list of MasterIds with both the US and CN. You then place the other filters in the WHERE clause and not in the subquery.

select distinct masterid
from yourtable t1
where exists (select Masterid
              from yourtable t2
              where cc in ('US', 'CN')
                and t1.masterid = t2.masterid
              group by masterid
              having count(distinct cc) = 2)
  and NLCLA = 1
  and NLDES = 1;

See SQL Fiddle with Demo

Solution 2

One way is using a CTE:

WITH CTE AS
(
    SELECT Masterid,CC,CLA,DES,NLCLA,NLDES,
        RN = ROW_NUMBER() OVER (PARTITION BY Masterid ORDER BY Masterid)
    FROM dbo.Table
    WHERE   CC IN('US', 'CN')
    AND     NLCLA = 1
    AND     NLDES = 1
)
SELECT Masterid FROM CTE WHERE RN = 1

Demo ( thanks to bluefeet for fiddle-data )

Note that the ROW_NUMBER partition function would be helpful if you want to get a specific row, for example always the latest record for each Masterid. But since you haven't provided a datetime column i've just ordered arbitrarily by Masterid.

Comments

[LeBlues]

what about if there is another row <br /> 53006144 CN 0 0 0 0

[Taryn]

@LeBlues what do you mean if there is another row? What would you except the result to be in that case? It will return the same result -- sqlfiddle.com/#!3/968a1/1

[Lamak]

@LeBlues If that row exists, then it meets the criteria of the WHERE, so it will also return that row

[Taryn]

@LeBlues see my edit, which will return only those records with both US and CN

[LeBlues]

it doesn't return 53006143

[LeBlues]

let me reface the question, i need to fine the masterids which has a CN and a US where there is NLDES = 1 and NLCLA = 1

[Taryn]

@LeBlues you are welcome, you had me scratching my head for a minute