求一分组自动产生序号的SQL语句解决办法

求一分组自动产生序号的SQL语句
表结构如下
col1 col2
a x1
a x2
a x3
b x1
b x2
...

现象通过col1字段分组产生自动排序号如下
id col1 col2
 1 a x1
 1 a x2
 1 a x3
 2 b x1
 2 b x2
...

请问如何编写SQL？

[解决办法]

SQL code

--> 测试数据：[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 allselect 'a','x2' union allselect 'a','x3' union allselect 'b','x1' union allselect 'b','x2'select ROW_NUMBER()over(partition by [col2] order by [col1]) as id,     col1,col2 from tbl order by col1,col2/*id    col1    col21    a    x11    a    x21    a    x32    b    x12    b    x2*/