Create identifier/counter based on some shared col

问题:

I want to create a calculated column based on a shared column but the calculated column should "restart" and be grouped based on a third column.

As described in the picture below Column 1 contains information belonging so a certain entity (some sort of ID). Column 2 contains the number of records that exists (1, 2 and 3). Column 3 contains the actual data (A, B or C) but the same value can exist between the three different records (separated by Column 2).

My previous process was discussed in this thread but as I went along I believe that it wasn't originally explained correctly.

Is there a way to create my desired result?

回答1:

Use windowing function DENSE_RANK() with an OVER() clause:

DECLARE @tbl TABLE(Column1 INT,Column2 INT,Column3 VARCHAR(100));
INSERT INTO @tbl VALUES(1,1,'A')
                      ,(1,2,'A') 
                      ,(1,3,'B') 
                      ,(2,1,'A') 
                      ,(2,2,'A') 
                      ,(2,3,'B') 
                      ,(3,1,'A') 
                      ,(3,2,'B') 
                      ,(3,3,'V');

SELECT *
      ,DENSE_RANK() OVER(PARTITION BY Column1 ORDER BY Column3) AS ComputedColumn
FROM @tbl;

The PARTITION BY will re-start the counter for each new value in column1, while the ORDER BY defines the ranking.

Hint: Do not paste pictures!

For your next question please follow my example to create a stand-alone example reproducing your issue and add the code you've tried yourself.