Filter rows using Row number and partition by columns and a business logic

For suppose lets consider the below set of table

Class	Class	rule	version	RuleAvailable
Class 1	Class1-Csx -02.01	Csx	02.01	Yes
Class1	Class1- Csx -01.01	Csx	01.01	Yes
Class1	Class1-CsxBusrule -02.01	CsxBusrule	02.01	Withdrawn
Class1	Class1-CsxBusrule -01.01	CsxBusrule	01.01	Yes
Class1	Class1 - Csxfw -01.01	Csxfw	01.01	Yes
Class2	Class2 - Csx - 02.01	Csx	02.01	Yes
Class2	Class2 - Csxbusrule -02.01	Csxbusrule	02.01	Yes
Class3	Class3 - Csxfw -02.01	Csxfw	02.01	Not Available
Class3	Class3 - Csxfw -01.01	Csxfw	01.01	Yes

Here, firstly we have to do partition of class and then rule and then by version

This is the order of rulesets

CSXBusrule>> CSX >> CSXFW

If the top version of the rule is withdrawn, we have to ignore that rule and go with the rule in next available order

If the top version of the rule is Not Available, we have to ignore that version and go with the next version in next available order

For each Unique Class, if we have three rules Csx ,CsxBusrule , Csxfw we should take Csxbusrule into consideration and then return the higher version of it. and if the higher version of Csxbusrule is withdrawn, we should not take any rows in csxbusrule, we should ignore csxbusrule and then take csx as per order

If for a class there is only rule Csxfw, higher version of that row should be returned

Below is the expected result

Class	Key	RUle	Version	ruleavailable
Class 1	Class1-Csx-02.01	Csx	02.01	Yes
Class2	Class2 - Csxbusrule -02.01	Csxbusrule	02.01	Yes
Class3	Class3 - Csxfw-01.01	Csxfw	01.01	Yes

Select 
  Key ,
  Class, 
  Rule , 
  Version 
FROM 
(
  SELECT 
    t.*,
    ROW_NUMBER() 
      OVER (PARTITION BY class ORDER BY 
                    CASE rule WHEN 'CSXBusRule' THEN 1 
                              WHEN 'CSX' THEN 2 
                              WHEN 'CSXFW' THEN 3 END, version DESC) AS rn 
  FROM table t 
  where 
    pyRuleAvailable='Yes'
) 

WHERE rn = 1,

tried this, but this doesnt include withdrawn logic, we have to ignore the rule and go with next rule order if the higher version of that rule is withdrawn, how to do it

Solution

From Oracle 12, you can set the priority of the rules and then use MATCH_RECOGNIZE for row-by-row pattern matching:

SELECT Key,
       Class, 
       Rule, 
       Version,
       RuleAvailable
FROM   (
         SELECT Key,
                Class, 
                Rule, 
                Version,
                RuleAvailable,
                DECODE(rule, 'CsxBusrule', 1, 'Csx', 2, 'Csxfw', 3) AS rule_priority,
                DECODE(ruleavailable, 'Withdrawn', 1, 'Yes', 2) AS available_priority
         FROM   table_name
       )
       MATCH_RECOGNIZE(
         PARTITION BY key
         ORDER BY rule_priority, available_priority, version DESC
         ALL ROWS PER MATCH
         PATTERN (^ {- ( withdrawn+ same_rule* )* -} not_withdrawn )
         DEFINE
           withdrawn AS RuleAvailable = 'Withdrawn',
           same_rule AS Rule = PREV(rule)
       )

In earlier versions, you can use:

SELECT Key,
       Class, 
       Rule, 
       Version,
       RuleAvailable
FROM   (
  SELECT Key,
         Class, 
         Rule, 
         Version,
         RuleAvailable,
         ROW_NUMBER() OVER (
           PARTITION BY key
           ORDER BY rule_priority, version DESC
         ) AS rn
  FROM   (
    SELECT Key,
           Class, 
           Rule, 
           Version,
           RuleAvailable,
           COUNT(CASE RuleAvailable WHEN 'Withdrawn' THEN 1 END) OVER (
             PARTITION BY key, rule
           ) AS num_withdrawn,
           DECODE(rule, 'CsxBusrule', 1, 'Csx', 2, 'Csxfw', 3) AS rule_priority
    FROM   table_name
  )
  WHERE  num_withdrawn = 0
)
WHERE  rn = 1;

Which, for the sample data:

CREATE TABLE table_name (Key, rule, version, RuleAvailable) AS
SELECT 'Class1', 'Csx',        '02.01', 'Yes' FROM DUAL UNION ALL
SELECT 'Class1', 'Csx',        '01.01', 'Yes' FROM DUAL UNION ALL
SELECT 'Class1', 'CsxBusrule', '02.01', 'Withdrawn' FROM DUAL UNION ALL
SELECT 'Class1', 'CsxBusrule', '01.01', 'Yes' FROM DUAL UNION ALL
SELECT 'Class1', 'Csxfw',      '01.01', 'Yes' FROM DUAL UNION ALL
SELECT 'Class2', 'Csx',        '02.01', 'Yes' FROM DUAL UNION ALL
SELECT 'Class2', 'CsxBusrule', '02.01', 'Yes' FROM DUAL UNION ALL
SELECT 'Class3', 'Csxfw',      '01.01', 'Yes' FROM DUAL

ALTER TABLE table_name ADD class VARCHAR2(50) GENERATED ALWAYS AS (
  key || '-' || rule || '-' || version
)

Both output:

KEY	CLASS	RULE	VERSION	RULEAVAILABLE
Class1	Class1-Csx-02.01	Csx	02.01	Yes
Class2	Class2-CsxBusrule-02.01	CsxBusrule	02.01	Yes
Class3	Class3-Csxfw-01.01	Csxfw	01.01	Yes

fiddle