Simulação Row_Number no SQL Server 2000
Tenho uma tabela de entrada de exemplo como
Declare @input TABLE(Name VARCHAR(8))
INSERT INTO @input(Name) values('Aryan')
INSERT INTO @input(Name) values('Aryan')
INSERT INTO @input(Name) values('Joseph')
INSERT INTO @input(Name) values('Vicky')
INSERT INTO @input(Name) values('Jaesmin')
INSERT INTO @input(Name) values('Aryan')
INSERT INTO @input(Name) values('Jaesmin')
INSERT INTO @input(Name) values('Vicky')
INSERT INTO @input(Name) values('Padukon')
INSERT INTO @input(Name) values('Aryan')
INSERT INTO @input(Name) values('Jaesmin')
INSERT INTO @input(Name) values('Vick')
INSERT INTO @input(Name) values('Padukon')
INSERT INTO @input(Name) values('Joseph')
INSERT INTO @input(Name) values('Marya')
INSERT INTO @input(Name) values('Vicky')
Também tenho uma tabela de registro como em
declare @t table(n int)
insert into @t select 1 union all select 2 union all
select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all select 8 union all
select 9 union all select 10 union all select 11 union all
select 12 union all select 13 union all select 14 union all
select 15 union all select 16 union all select 17 union all
select 18 union all select 19 union all select 20
No Sql Server 2005 se eu fizer como
Select rn, name from (
select ROW_NUMBER()over (order by Name) as rn , * from @input) x
where rn % 2 <> 0
Recebo a saída como
rn name
1 Aryan
3 Aryan
5 Jaesmin
7 Jaesmin
9 Joseph
11 Padukon
13 Vick
15 Vicky
Bu Estou restrito ao servidor SQL 2000. Como posso obter a mesma saíd
Eu tentei com
SELECT name, (SELECT COUNT(*) FROM @input AS i2 WHERE i2.Name <= i1.Name) As rn
FROM @input AS i1
mas a saída está errada
name rn
Aryan 4
Aryan 4
Joseph 9
Vicky 16
Jaesmin 7
Aryan 4
Jaesmin 7
Vicky 16
Padukon 12
Aryan 4
Jaesmin 7
Vick 13
Padukon 12
Joseph 9
Marya 10
Vicky 16