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

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:

UID	A1	A2	A3	A4	R1ID	R2ID
7	Null	Null	C3	null	1	1
8	Null	Null	C3	d7	1	2
1	A1	B1	C1	null	2	1
2	A1	B1	C1	D1	2	2
4	A1	B1	C1	D2	2	3
3	A2	B2	C2	D1	3	1
5	A2	B2	C2	D4	3	2
6	A2	B2	C2	D6	3	3

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)?

CodePudding user response：

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