Adding Sequence number in SQL

1 Sep 2005 6:26 PM

kimmal

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

1 Sep 2005 6:53 PM

Alejandro Mesa

Which 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/default.aspx?scid=kb;en-us;186133

AMB

Show quote

"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
>
>