SQL Server group by 2 columns - generate sequence number

Question

I have table like below,

create table #temp
(
    Name varchar(100),
    Id int,
)

Insert into #temp values ('AAA',101)
Insert into #temp values ('AAA',102)
Insert into #temp values ('AAA',102)
Insert into #temp values ('AAA',103)
Insert into #temp values ('AAA',103)
Insert into #temp values ('BBB',201)
Insert into #temp values ('BBB',201)
Insert into #temp values ('BBB',202)
Insert into #temp values ('BBB',203)

Expected output:

Name  Id      Seq 
-------------------
AAA   101      1
AAA   102      2
AAA   102      2
AAA   103      3
AAA   103      3
BBB   201      1
BBB   201      1
BBB   202      2
BBB   203      3

I want to generate sequence number based on name and Id. If Id is same, same sequence number needs to be assign.

Answer 1

Use DENSE_RANK:

SELECT
    Name,
    Id,
    DENSE_RANK() OVER (PARTITION BY Name ORDER BY Id) Seq
FROM #temp;

Demo