Get all records that matches a specific criteria of another records over multiple partitions in the-CodePudding

I have the following view/table T:

 -------- -------------- ------- ---------- 
| row_id | batch_status | class | batch_id |
 -------- -------------- ------- ---------- 
| 1      | new          | K1    | B1       |
 -------- -------------- ------- ---------- 
| 2      | proc         | K1    | B2       |
 -------- -------------- ------- ---------- 
| 3      | proc         | K1    | B3       |
 -------- -------------- ------- ---------- 
| 4      | proc         | K1    | B1       |
 -------- -------------- ------- ---------- 
| 5      | finish       | K1    | B1       |
 -------- -------------- ------- ---------- 
| 6      | new          | K2    | B5       |
 -------- -------------- ------- ---------- 
| 7      | proc         | K2    | B2       |
 -------- -------------- ------- ---------- 
| 8      | new          | K3    | B2       |
 -------- -------------- ------- ---------- 
| 9      | proc         | K3    | B5       |
 -------- -------------- ------- ---------- 
| 10     | finish       | K3    | B1       |
 -------- -------------- ------- ---------- 
| 11     | finish       | K3    | B2       |
 -------- -------------- ------- ---------- 
| 12     | new          | K4    | B7       |
 -------- -------------- ------- ---------- 
| 13     | new          | K5    | B2       |
 -------- -------------- ------- ----------

where records are distributed over 5 classes: k1 -> k5.

Now for each of those class groups I need to display batches with ids that matches the batch_id of that one with status 'new'. that's means what need to be excluded is (red crossline). records those not striked-through are ones need to be displayed:

RDBMS: Oracle 12c

CodePudding user response：

You can use COUNT() OVER () analytic function with conditional in order to check the existence for any batch_status equals to the value new per each class and batch_id such as

SELECT row_id, batch_status, class, batch_id
  FROM (SELECT t.*,
               COUNT(CASE
                       WHEN batch_status = 'new' THEN
                        1
                     END) OVER(PARTITION BY class, batch_id) AS cnt
          FROM t)
 WHERE cnt > 0
 ORDER BY class

or without using a subquery(as your DB version is 12c)

SELECT t.*,
       COUNT(CASE
               WHEN batch_status = 'new' THEN
                    0
                END) OVER (PARTITION BY class, batch_id) AS cnt
  FROM t
 ORDER BY 1-SIGN(cnt)
 FETCH FIRST 1 ROW WITH TIES

Demo

CodePudding user response：

Is it: Select this row if there exists, in the same table, a row with the same batch_id and class and batch_status=new.

Select *
From T as T1
Where
Exists (select 1
From T as T2
Where T2.batch_status='new'
And T2.batch_id=T1.batch_id
And T2.class=T1.class)