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:

• 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:

CodePudding user response：

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
)