add sequence number - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

add sequence number

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2008-06-10 : 05:49:07

Hi,

I have a table with CustID and a date (see table1) - i would like to add a sequenceID to the data so as i have a table looking like table 2. Can anyone suggest a t-sql statement that can generate the seqID as seen in table 2?

Thanks

---------------------------------------------
/*
Please paste T-SQL into query window
*/
---------------------------------------------
CREATE TABLE #table1

(
custID nvarchar(6),
dateorder [datetime] NULL
)

GO
SET ANSI_PADDING OFF

Insert into #table1
select '012345','2008-04-19 00:00:00.000' UNION all
select '012345','2008-04-19 00:00:00.000' UNION all
select '012345','2008-04-21 00:00:00.000' UNION all
select '012345','2008-04-21 00:00:00.000' UNION all
select '012345','2008-04-21 00:00:00.000' UNION all
select '987654','2008-04-20 00:00:00.000' UNION all
select '987654','2008-04-20 00:00:00.000' UNION all
select '987654','2008-04-22 00:00:00.000'

select * from #table1

--This is the table i would like to insert my data into
CREATE TABLE #table2

(
SeqNo int,
custID nvarchar(6),
dateorder [datetime] NULL
)

Insert into #table2
select 1, '012345','2008-04-19 00:00:00.000' UNION all
select 1, '012345','2008-04-19 00:00:00.000' UNION all
select 2, '012345','2008-04-21 00:00:00.000' UNION all
select 2, '012345','2008-04-21 00:00:00.000' UNION all
select 2, '012345','2008-04-21 00:00:00.000' UNION all
select 1, '987654','2008-04-20 00:00:00.000' UNION all
select 1, '987654','2008-04-20 00:00:00.000' UNION all
select 2, '987654','2008-04-22 00:00:00.000'
select * from #table2

drop table #table1
drop table #table2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 06:36:24

[code]select (select count(distinct custID) from #table1 where custID=t1.custID AND dateorder<t1.dateorder)+1 as seq,*
from #table1 t1[/code]

Not sure how subquery will perform for large table.

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-06-10 : 06:45:53

Can we use Dense_Rank in the above query. As per BOl "Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.". I tried following
select Dense_Rank() over (partition by custID,dateorder
order by custID,dateorder) as Number,custID,dateorder from #table1
and got
Number custID dateorder
-------------------- ------ -----------------------
1 012345 2008-04-19 00:00:00.000
1 012345 2008-04-19 00:00:00.000
1 012345 2008-04-21 00:00:00.000
1 012345 2008-04-21 00:00:00.000
1 012345 2008-04-21 00:00:00.000
1 987654 2008-04-20 00:00:00.000
1 987654 2008-04-20 00:00:00.000
1 987654 2008-04-22 00:00:00.000

If we are partioning , then, why is result having 1. If one can point mistake I am doing or anything incorrect I am assuming.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 07:00:57

Windowed functions are not available in SQL Server 2000.

E 12°55'05.25"
N 56°04'39.16"

DLTaylor
Posting Yak Master

136 Posts

Posted - 2008-06-10 : 08:05:39

Thanks for all your help.
visakh16 - using you method it works great.
Fortunately my tables are not too vast so the technique works great.
It always amazes me how people dream up these solutions :-) Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources