Adding Sequence number in SQL

Being a newbie to SQL programming, was hoping someone would be able to
guide me in the right direction.
I have a table that has the following data
Center Emp_ID
11 112
11 2254
11 346
12 456
12 138
13 8761
Etc, etc
I want to add a Sequence number which resets to 1 by center. So I want
it to look like this
List_ID Center Emp_ID
1 11 112
2 11 2254
3 11 346
1 12 456
2 12 138
1 13 8761
I've done the following:
SELECT TOP 100 PERCENT FIRST_NAME, LAST_NAME,
(SELECT COUNT(*)
FROM dbo.Planning_Heads e2
WHERE e2.ACCT_CD <=
dbo.Planning_Heads.ACCT_CD) AS List_ID, ACCT_CD
FROM dbo.Planning_Heads
ORDER BY ACCT_CD
but it's no "restarting" the List_Id or incrementing it properly
Sorry for the long post, but thought it would be best to give as much
info as I couldWhich criteria can we use to tell sql server that Emp_ID = 346 goes after
Emp_ID = 2254, other than analyzing row by row?.
In db [northwind], the orders for each customer are stored in table [orders]
and the column [orderid] is an identity one that we can use to sort them
chronologically by customer.
use northwind
go
select
count(*) as rank,
a.orderid,
a.employeeid
from
dbo.orders as a
inner join
dbo.orders as b
on a.employeeid = b.employeeid
and a.orderid >= b.orderid
group by
a.employeeid,
a.orderid
order by
a.employeeid,
rank
go
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"kimmal" wrote:

> Being a newbie to SQL programming, was hoping someone would be able to
> guide me in the right direction.
> I have a table that has the following data
> Center Emp_ID
> 11 112
> 11 2254
> 11 346
> 12 456
> 12 138
> 13 8761
> Etc, etc
> I want to add a Sequence number which resets to 1 by center. So I want
> it to look like this
> List_ID Center Emp_ID
> 1 11 112
> 2 11 2254
> 3 11 346
> 1 12 456
> 2 12 138
> 1 13 8761
> I've done the following:
> SELECT TOP 100 PERCENT FIRST_NAME, LAST_NAME,
> (SELECT COUNT(*)
> FROM dbo.Planning_Heads e2
> WHERE e2.ACCT_CD <=
> dbo.Planning_Heads.ACCT_CD) AS List_ID, ACCT_CD
> FROM dbo.Planning_Heads
> ORDER BY ACCT_CD
> but it's no "restarting" the List_Id or incrementing it properly
> Sorry for the long post, but thought it would be best to give as much
> info as I could
>