CODEWITHSUNDEEP
sqloracle-database
Amit
Amit

Reputation: 101

How can I get key values belonging to multiple groups in my source data?

How can I get the RM_ID values the belonging to multiple groups in my source data?

I have two columns in my table: RM_ID and Group, with and data like this:

RM_ID GROUP
100 DEF
100 DEF
200 ABC
200 ABC
200 DEF
300 XYZ
300 XYZ
300 ABC
400 PQR
400 PQR

Desired Result:

RM_ID GROUP
200 ABC
200 DEF
300 XYZ
300 ABC

Upvotes: 3

Views: 141

Answers (1)

VvdL
VvdL

Reputation: 3210

You can do it in a couple of steps. I'll use some CTE's to make it clearer.

First get the distinct pairs of values, Then use a window function to count the number of groups per RM_ID, and then select the records with more than 1 group.

WITH DISTINCT_PAIRS AS (
  SELECT DISTINCT
    RM_ID, 
    GROUP_
  FROM YOUR_TABLE), 

GROUP_COUNTER AS (
  SELECT 
    RM_ID, 
    GROUP_, 
    COUNT(1) OVER (PARTITION BY RM_ID) AS NO_OF_UNIQUE_GROUPS
  FROM DISTINCT_PAIRS
)

SELECT 
  RM_ID, 
  GROUP_
FROM GROUP_COUNTER 
WHERE NO_OF_UNIQUE_GROUPS > 1

Alternatively you can do it like this, a little less verbose:

SELECT DISTINCT
  RM_ID, 
  GROUP_
FROM YOUR_TABLE
WHERE RM_ID IN (
  SELECT 
    RM_ID
  FROM YOUR_TABLE
  GROUP BY RM_ID
  HAVING COUNT(DISTINCT GROUP_) > 1
)

Upvotes: 3

Related Questions