sql server programming: Is it possible to accomplish following.

create table Orders

(

RowID int identity,

Batchid VARCHAR(10),

Product varchar(10)

)

go

insert into [orders](BatchID,Product)

values('10X','Cycle')

insert into [orders](BatchID,Product)

values('10X','Book')

insert into [orders](BatchID,Product)

values('10X','Phone')



insert into [orders](BatchID,Product)

values('20Y','Paper')

insert into [orders](BatchID,Product)

values('20Y','Monitor')

insert into [orders](BatchID,Product)

values('20Y','Pencil')

go

select row_number() over (PARTITION BY BATCHID order by RowID)[Seq],BatchID
from [Orders]

go



Seq BatchID
-------------------- ----------
1 10X
2 10X
3 10X
1 20Y
2 20Y
3 20Y


how can i achive following result

Seq BatchID GroupNo.
-------------------- ------------------ -------------
1 10X 1
2 10X 1
3 10X 1
1 20Y 2
2 20Y 2
3 20Y 2

thx. a lot.

[quoted text, click to view]

[quoted text, click to view]

select
row_number() over (PARTITION BY BATCHID order by RowID)[Seq],
BatchID,
dense_rank() over (order BY BATCHID)
from [Orders]