I've been struggling with this for a while now. I have an Oracle table like below.

I want to introduce a rank just for Catalog = 1 and Code = G or G_M based on earliest Event_DT. All the EVENT_DT are the same. I want the resulting table to look like this:

This is what I have tried:

SELECT CATALOG, CODE, EVENT_DT,
CASE WHEN CODE NOT LIKE 'G%' THEN 0 ELSE ROW_NUMBER() OVER (PARITION BY CATALOG ORDER BY EVENT_DT ASC) END AS C_RANK
FROM TABLE
WHERE CATALOG = 1;

This results in the following table. Row number 6 should have a rank of 3. However, row number 5, which has catalog = 1 and code = U is also counted towards the ranking. I thought the case statement will introduce a 0 for row number 5, it looks have done that but also counted towards the ranking.

CodePudding user response：

If you onöy after the G, you can use

SELECt "ROW_NUM", "CATALOG", "CODE", "EVENT_DT"
, CASE WHEN substr("CODE",1,1) = 'G' 
  THEN ROW_NUMBER() OVER (ORDER BY substr("CODE",1,1),"ROW_NUM") ELSE 0 END AS "c_rwn"
FROM tab1 ORDER BY "ROW_NUM"

ROW_NUM | CATALOG | CODE | EVENT_DT  | c_rwn
------: | ------: | :--- | :-------- | ----:
      1 |       1 | G    | 30-AUG-21 |     1
      2 |       2 | M    | 30-AUG-21 |     0
      3 |       1 | G_M  | 30-AUG-21 |     2
      4 |       3 | U    | 30-AUG-21 |     0
      5 |       1 | U    | 30-AUG-21 |     0
      6 |       1 | G    | 30-AUG-21 |     3
      7 |       2 | G_M  | 30-AUG-21 |     4

db<>fiddle here

As you only want catalog 1

SELECt "ROW_NUM", "CATALOG", "CODE", "EVENT_DT"
, CASE WHEN substr("CODE",1,1) = 'G' AND "CATALOG" = 1 
  THEN ROW_NUMBER() OVER (ORDER BY substr("CODE",1,1),"ROW_NUM") ELSE 0 END AS "c_rwn"
FROM tab1 ORDER BY "ROW_NUM"

ROW_NUM | CATALOG | CODE | EVENT_DT  | c_rwn
------: | ------: | :--- | :-------- | ----:
      1 |       1 | G    | 30-AUG-21 |     1
      2 |       2 | M    | 30-AUG-21 |     0
      3 |       1 | G_M  | 30-AUG-21 |     2
      4 |       3 | U    | 30-AUG-21 |     0
      5 |       1 | U    | 30-AUG-21 |     0
      6 |       1 | G    | 30-AUG-21 |     3
      7 |       2 | G_M  | 30-AUG-21 |     0

db<>fiddle here

CodePudding user response：

You may try adding the filter expression CODE LIKE 'G%' to your partition clause. This will create a sub-partition of true/false values. Moreover, your data may not order automatically as how you've shown it in the example if all rows have the same date. I've included an additional field ROW_NUM to order in the example. Based on your actual dataset, you may substitute this with a suitable field.

Eg.

SELECT
    t.*,
    CASE
        WHEN (CATALOG=1) AND (CODE LIKE 'G%') THEN ROW_NUMBER() OVER (
            PARTITION BY CATALOG,CASE WHEN (CODE LIKE 'G%') THEN 1 ELSE 0 END
            ORDER BY EVENT_DT,ROW_NUM
        )
    END as C_RANK
FROM
    my_table t
ORDER BY
    ROW_NUM;

View working demo on DB Fiddle

Let me know if this works for you.