sql server - Is it possible to add a identity to a GROUP BY using SQL? -

is possible add identity column group each duplicate has identity number?

my original data looks this:

1    aaa  [timestamp] 2    aaa  [timestamp] 3    bbb  [timestamp] 4    ccc  [timestamp] 5    ccc  [timestamp] 6    ccc  [timestamp] 7    ddd  [timestamp] 8    ddd  [timestamp] 9    eee  [timestamp] .... 

and want convert to:

1    aaa   1 2    aaa   2 4    ccc   1 5    ccc   2 6    ccc   3 7    ddd   1 8    ddd   2 ... 

the solution was:

create procedure [dbo].[rankit] begin set nocount on;  select  *, rank() over(partition col2 order timestamp desc) ranking  mytable;  end 

you try using row_number if using sql server 2005

declare @table table(         id int,         val varchar(10) )  insert @table select 1,'aaa' insert @table select 2,'aaa' insert @table select 3,'bbb'  insert @table select 4,'ccc'  insert @table select 5,'ccc'  insert @table select 6,'ccc'  insert @table select 7,'ddd'  insert @table select 8,'ddd'  insert @table select 9,'eee'   select  *,         row_number() over(partition val order val)    @table