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

user_12569641 0
Viewed 33

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

  • I want to identify all the RM_ID who belong to more than one group, and also retrieve the groups' names'.

  • How can I achieve that in Oracle?

    • Will the LEAD/LAG function help here?

Desired Result:

RM_ID GROUP
200 ABC
200 DEF
300 XYZ
300 ABC
oracle sql
1 Answers

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
)
user_155890100
Related