Generate a ID column based on group by as well as the row number

I am trying to build a database for which I would like the data to be formatted as mentioned in the expected output. Following is the DDL

CREATE TABLE TEST
(UID int, A1 VARCHAR(10), A2 VARCHAR(10), A3 VARCHAR(10), A4 VARCHAR(10));

INSERT INTO TEST VALUES
(1, 'A1' , 'B1', 'C1', NULL),
(2, 'A1' , 'B1', 'C1', 'D1'),
(3, 'A2' , 'B2', 'C2', 'D1'),
(4, 'A1' , 'B1', 'C1', 'D2'),
(5, 'A2' , 'B2', 'C2', 'D4'),
(6, 'A2' , 'B2', 'C2', 'D6'),
(7, NULL, NULL, 'C3' , NULL),
(8,  NULL, NULL, 'C3' , 'd7');

what I’m looking for is to create two ID columns – one based on a Group by of combination of columns A1,A2,A3 and the other for the row_number within those three columns

Expected results:

I tried

select *,
       row_number() over (partition by A1,A2,A3 ORDER BY A4) AS R2ID 
FROM TEST;

This just gave me the last column of the expected result(R2ID) , but how can I get both the expected columns
(R1ID n R2ID)?

>Solution :

You can get your first ID by using DENSE_RANK() and ordering by your "Grouping" columns and, as you have said, your second ID using ROW_NUMBER() and partitioning by those same columns. This gives your expected results:

SELECT  t.UID,
        t.A1,
        t.A2,
        t.A3,
        t.A4,
        R1ID = DENSE_RANK() OVER (ORDER BY t.A1, t.A2, t.A3),
        R2ID = ROW_NUMBER() OVER (PARTITION BY t.A1, t.A2, t.A3 ORDER BY t.A4)
FROM    TEST AS t;

Example on db<>fiddle