How to create rownum in SQL Server

Below is the example that I want to do:

declare @table table(col1 varchar(10),col2 varchar(10));
insert into @table(col1,col2) values ('a1','5340');
insert into @table(col1,col2) values ('a1','3340');
insert into @table(col1,col2) values ('a1','9185340');
insert into @table(col1,col2) values ('b1','1110');

Here is a table and sample data. Now how I want result is as below:

select * from @table

col1    col2      seq
a1  5340       1
a1  3340       2
a1  9185340    3
b1  1110       1

If you noticed here, the SEQ is reset back again to 1 for new value of COL1. And I don't want to change the order of value in COL2. ie. value 5340 should be 1 and so on.

This is jus开发者_运维技巧t a sample data. But the real data comes from another table so the values are not fixed to only 4 rows.

Any help would be appreciated. Thank You

Just use a regular IDENTITY column as your SEQ:

declare @table table(raw_seq int IDENTITY(1,1), col1 varchar(10),col2 varchar(10));

then use

ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY raw_seq) AS seq

In order to get the value you want.

Use the row_number() analytic function.

select
  col1,
  col2,
  row_number() over (partition by col1 order by col1) seq
from
  table