Simulate Row_Number in Sql server 2000 using tally table
I have a sample input table as DECLARE @input TABLE ( Name VARCHAR(8) ) INSERT INTO @input ( Name ) VALUES ( 'Aryan' ), ( 'Aryan' ), ( 'Joseph' ), ( 'Vicky' ), ( 'Jaesmin' ), ( 'Aryan' ), ( 'Jaesmin' ), ( 'Vicky' ), ( 'Padukon' ), ( 'Aryan' ), ( 'Jaesmin' ), ( 'Vick' ), ( 'Padukon' ), ( 'Joseph' ), ( 'Marya' ), ( 'Vicky' ) Also I have a tally table as under DECLARE @t TABLE ( n INT ) INSERT INTO @t VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20) In Sql Server 2005 if I do as SELECT rn , name FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY Name ) AS rn , * FROM @input ) x WHERE rn % 2 <> 0 I get the output as rn name 1 Aryan 3 Aryan 5 Jaesmin 7 Jaesmin 9 Joseph 11 Padukon 13 Vick 15 Vicky But I am restricted to Sql server 2000. How can I get the same output?
Best Answer
can you move the data into another temp table with an identity column
declare @NewTable table (id int identity(1,1), name varchar(8))
insert into @NewTable (name)
select name from @input order by name
select id as rn, name from @NewTable
where id % 2 0
Is there any other way without using the identity column? I have tried this query though the answer I am getting is wrong SELECT name, (SELECT COUNT(*) FROM @input AS i2 WHERE i2.Name <= i1.Name) As rn FROM @input AS i1
You will struggle to try and get unique row numbers from the name as it is not unique.
The code you have tried works if all the elements are unique - and is actually the way I would have done this - but there is nothing in the data to uniquely identify the row
