oracle - SQL Query to get specific child records

Question

I have a requirement to get child table records based on parent table search criteria but they need to be distinct and output should be like below:

Table A, have three rows. Row one is for generic rules, Row 2 is for specific Category and Row 3 is for Specific Branch, Category and Sub-Category. Now, my output should consists of the rules which are specific to generic. Below are the rules for the output:

• Input to the query will be Branch, Category and Sub-Category
• Each record-set in Table-A is comprised of 03 rows Row 1 has Branch but Category and Sub-Category as Null Row 2 has Branch and Category Sub-Category as Null Row 3 has Branch, Category and Sub-Category.
• Each Row in a record-set of Table-A has child records in Table-B
• Record with Branch only (Row 1), have generic records and these records can also be child records of Row 2 and Row 3
• Record with Branch and Category Sub-Category as Null (Row 2) has child records in Table-B and they are overriding child records of Row 1
• Record with Branch, Category and Sub-Category (Row 3) has child records in Table-B and they are overriding child records of Row 1 and Row 2.
• All child records of Row 1,2 & 3 will be part of the output but if a child is present in Row 3 then despite if it is present in other Rows output will consists of child record of Row 3
• If a child record is present in Row 1 & 2 but not in 3 then output will have child record of Row 2
• if a child record is present in Row 1 but not in Row 2 & 3 then it will be part of output.

Now,

• In the sample output, 'Pay' is present in Row 1,2 and 3 but in the output we are considering child record of Row 3 as it overrides both Record 1 & 2
• 'Discount' is present in Record 1 & 3 but output includes child of Row 3
• 'Items' is not part of Row 1 and Row 2 childs but as it is present in Row 3 so it will be part of output
• 'Paris' is only part of Row 2 but as it is not overriden by Row 2 so it is part of output as it is

I have tried following query but it is not giving the required output:

SELECT DISTINCT RULE,
                value
FROM   siebel.b rxm
WHERE  par_row_id IN (SELECT row_id
                      FROM   siebel.a
                      WHERE  ( branch = 'Civil'
                               AND category = 'C.M.> (Civil)'
                               AND sub_category IS NULL )
                              OR ( branch = 'Civil'
                                   AND category = 'C.M. (Civil)'
                                   AND sub_category = 'Pauper' )
                              OR ( branch = 'Civil'
                                   AND category IS NULL
                                   AND sub_category IS NULL ))

I am using Oracle as RDBMS.

Schema statements:

Create Table A (ROW_ID int, BRANCH varchar(50), CATEGORY varchar(50), SUB_CATEGORY varchar(50))
Create Table B (PAR_ROW_ID int, RULE varchar(50), Value varchar(50))

INSERT INTO A (ROW_ID, BRANCH)
VALUES (1,'Civil')
INSERT INTO A (ROW_ID, BRANCH, CATEGORY)
VALUES (2,'Civil','C.M. (Civil)')
INSERT INTO A (ROW_ID, BRANCH, CATEGORY, SUB_CATEGORY)
VALUES (3,'Civil','C.M. (Civil)','Pauper')

INSERT INTO B (PAR_ROW_ID, RULE, VALUE)
VALUES (1,'Pay','10')
INSERT INTO B (PAR_ROW_ID, RULE, VALUE)
VALUES (1','Days','25')
INSERT INTO B (PAR_ROW_ID, RULE, VALUE)
VALUES (1,'Discount','20')
INSERT INTO B (PAR_ROW_ID, RULE, VALUE)
VALUES (2,'Pairs','5')
INSERT INTO B (PAR_ROW_ID, RULE, VALUE)
VALUES (2,'Pay','30')
INSERT INTO B (PAR_ROW_ID, RULE, VALUE)
VALUES (3,'Pay','15')
INSERT INTO B (PAR_ROW_ID, RULE, VALUE)
VALUES (3,'Discount','20')
INSERT INTO B (PAR_ROW_ID, RULE, VALUE)
VALUES (3,'items','30')

Answer 1

SELECT MAX( par_row_id ) AS par_row_id,
       rule,
       MAX( value ) KEEP ( DENSE_RANK LAST OVER ORDER BY par_row_id ) AS value
FROM   table_b
GROUP BY rule

Or:

SELECT par_row_id,
       rule,
       value
FROM   (
  SELECT b.*,
         ROW_NUMBER() OVER ( PARTITION BY rule ORDER BY par_row_id DESC ) AS rn
  FROM   table_b b
)
WHERE  rn = 1;