I want to filter C2 to value of B, but if the B doesn't exist so I need C and if the C doesn't exist so I need D

So the result will be as the following:

CodePudding user response：

Use the RANK analytic function (if you want to include duplicate rows if there are multiple B values for a c1 group, otherwise use the ROW_NUMBER analytic function for a single row) and then filter to only include the first rank in each partition:

SELECT c1, c2, c3
FROM   (
  SELECT t.*,
         RANK() OVER (PARTITION BY c1 ORDER BY c2) AS rnk
  FROM   table_name t
  WHERE  c2 IN ('B', 'C', 'D')
)
WHERE  rnk = 1;

Which, for the sample data:

CREATE TABLE table_name (C1, C2, C3) AS
  SELECT 1, 'A', 1000 FROM DUAL UNION ALL
  SELECT 1, 'B', 2000 FROM DUAL UNION ALL
  SELECT 1, 'C', 3000 FROM DUAL UNION ALL
  SELECT 1, 'D', 4000 FROM DUAL UNION ALL
  SELECT 2, 'A', 1000 FROM DUAL UNION ALL
  SELECT 2, 'D', 4000 FROM DUAL UNION ALL
  SELECT 2, 'E', 5000 FROM DUAL UNION ALL
  SELECT 3, 'A', 1000 FROM DUAL UNION ALL
  SELECT 3, 'B', 2000 FROM DUAL UNION ALL
  SELECT 3, 'D', 4000 FROM DUAL UNION ALL
  SELECT 3, 'E', 5000 FROM DUAL;

Outputs:

fiddle