How to rank only a subset of data in SQL

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

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

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:

ROW_NUM	CATALOG	CODE	EVENT_DT	C_RANK
1	1	G	30-AUG-21	1
2	2	M	30-AUG-21
3	1	G_M	30-AUG-21	2
4	3	U	30-AUG-21
5	1	U	30-AUG-21
6	1	G	30-AUG-21	3
7	2	G_M	30-AUG-21

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.

ROW_NUM	CATALOG	CODE	EVENT_DT	C_RANK
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	4
7	2	G_M	30-AUG-21	0

Solution

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;

row_num	CATALOG	code	event_dt	c_rank
1	1	G	2021-08-30T00:00:00.000Z	1
2	2	M	2021-08-30T00:00:00.000Z
3	1	G_M	2021-08-30T00:00:00.000Z	2
4	3	U	2021-08-30T00:00:00.000Z
5	1	U	2021-08-30T00:00:00.000Z
6	1	G	2021-08-30T00:00:00.000Z	3
7	2	G_M	2021-08-30T00:00:00.000Z

View working demo on DB Fiddle

Let me know if this works for you.