Ranking subcategories while keeping order of ID

I have the following table:

+----+------+------+
| id | cat1 | cat2 |
+----+------+------+
|  1 | A    | foo  |
|  2 | A    | foo  |
|  3 | A    | bar  |
|  4 | B    | sci  |
|  5 | B    | ble  |
|  6 | B    | ble  |
+----+------+------+

I would like to rank the subcategory (cat2).

Desired outcome:

+----+------+------+------+
| id | cat1 | cat2 | res  |
+----+------+------+------+
|  1 | A    | foo  |    1 |
|  2 | A    | foo  |    1 |
|  3 | A    | bar  |    2 |
|  4 | B    | sci  |    1 |
|  5 | B    | ble  |    2 |
|  6 | B    | ble  |    2 |
+----+------+------+------+

I use DENSE_RANK with PARTITION BY to get the following result:

+----+------+------+------+
| id | cat1 | cat2 | res  |
+----+------+------+------+
|  1 | A    | foo  |    2 |
|  2 | A    | foo  |    2 |
|  3 | A    | bar  |    1 |
|  4 | B    | sci  |    2 |
|  5 | B    | ble  |    1 |
|  6 | B    | ble  |    1 |
+----+------+------+------+

Statement:

SELECT DENSE_RANK() OVER (PARTITION BY cat1 ORDER BY cat2 asc) as res, t.* 
  FROM mytable t 
 ORDER BY id;

As you can see, the only thing I’m missing is the order of the result. Currently, the rank is based on the alphabetic order of cat2. However, I want to preserve the order given by the id’s (see desired outcome). Simply changing the ORDER BY of my DENSE_RANK will not do the trick.

Answer

WITH cte AS ( SELECT MIN(id) id, cat1, cat2
              FROM test
              GROUP BY cat1, cat2 )
SELECT t1.id,
       t1.cat1,
       t1.cat2,
       DENSE_RANK() OVER (PARTITION BY t1.cat1 ORDER BY t2.id asc) as res
FROM test t1 
JOIN cte t2 USING (cat1, cat2)
ORDER BY t1.id;

fiddle

Of course, CTE may be converted to subquery.