devxlogo

Sequence a Number Field Without an Identity Column

Sequence a Number Field Without an Identity Column

To begin, create a table with dummy records:

Create table Table1 (    [Record Value] varchar(20),    [Record Date] smalldatetime)goinsert into table1 values ('Record 1','07/21/2003')insert into table1 values ('Record 2','07/22/2003')insert into table1 values ('Record 3','07/23/2003')insert into table1 values ('Record 4','07/24/2003')insert into table1 values ('Record 5','07/25/2003')insert into table1 values ('Record 6','07/26/2003')

Next, use this TSQL statement to sequence the number field:

 select [Record Value],[Record Date],SeqNumber=(select count(*) from Table1 //as MyTable where MyTable.[Record Date]

devx-admin

Share the Post: