CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Kric
Kric

Reputation: 103

Within the same group find and exclude records that have the same parent ID for certain types

I have a table like following:

GroupID    ParentID    Type

   1         ABC       IND
   1         ABC       IND
   1         CDE       ORD
   1         EFG       STD
   2         ZZZ       IND
   2         ZZZ       IND
   2         ZZZ       IND
   3         YYY       COR
   3         YYY       COR

I need to exclude those records that are in the same group, having the same parent ID and the type is IND or COR. But I need to keep those groups that have different parent ID and the type is not IND or COR.

So the result I want to get would be the following:

GroupID    ParentID    Type

   1         ABC       IND
   1         ABC       IND
   1         CDE       ORD
   1         EFG       STD

Somehow I am thinking to use Rank () over(partition by GroupID order by ParentID), but it won't give me the results that I want.

Any thoughts? PS: This table has 5 Million+ records. Looking for the effective way to deal with it.

Thanks

Upvotes: 1

Views: 249

Answers (2)

AgentSQL
AgentSQL

Reputation: 2940

How is 

 1         ABC       IND

in result set? here type is IND and you mentioned the result set should not have type IND or COR?

Upvotes: 0

Hogan
Hogan

Reputation: 70523

The following gives you a list of the groupIDs you want to exclude

SELECT GroupID
FROM
(
  SELECT GroupID, 
         COUNT(DISTINCT ParentID) AS PCount, COUNT(DISTINCT TypeCode) as TCount,
         MAX(TypeCode) AS tCode
  FROM tablename
  GROUP BY GroupID
) t
WHERE PCount = 1 AND TCount = 1
   AND (tCode = 'IND' OR tCode = 'COR')

Now select everything else

SELECT * 
FROM tableName
WHERE GroupID not in (
  SELECT GroupID
  FROM
  (
    SELECT GroupID, 
           COUNT(DISTINCT ParentID) AS PCount, COUNT(DISTINCT TypeCode) as TCount,
           MAX(TypeCode) AS tCode
    FROM tablename
    GROUP BY GroupID
  ) t
  WHERE PCount = 1 AND TCount = 1
    AND (tCode = 'IND' OR tCode = 'COR')
)

Test with fiddle --> http://sqlfiddle.com/#!3/f1d4f/15/0

Upvotes: 1

Related Questions