--> 测试数据：[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([col1] varchar(1),[col2] varchar(2))
insert [tbl]
select 'a','x1' union all
select 'a','x2' union all
select 'a','x3' union all
select 'b','x1' union all
select 'b','x2'

select ROW_NUMBER()over(partition by [col2] order by [col1]) as id,
     col1,col2 from tbl order by col1,col2
/*
id    col1    col2
1    a    x1
1    a    x2
1    a    x3
2    b    x1
2    b    x2
*/