GET IDENTTITY USING CROSS JOIN AND SUB QUERY

Compatibility:- SQL Server 7,6.5 & earlier

set nocount on
go
create table #test (description char(5))
insert #test values('aaaaa')
insert #test values('baaab')
insert #test values('haaab')
insert #test values('mmmmm')
insert #test values('taaar')
insert #test values('xxxxz')
insert #test values('zzzza')
go
-- generate sequence using cross join. j
-- ust an example, may not be the best way
-- always
select t1.description,
sum(case when t2.description <= t1.description then 1 else 0 end) as sequence
from #test t1 cross join #test t2
group by t1.description
order by t1.description
-- using a sub-query...
select t1.description,
(select count(*) from #test t2 where t2.description <= t1.description) as sequence
from #test t1
order by t1.description