Simulate Row_Number in Sql server 2000 using tally table

Question

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?

Answer 1

0

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

answered Jun 27, 2011 at 02:40 AM

Kev Riley ♦♦
64k ● 48 ● 61 ● 81

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

Jun 27, 2011 at 02:42 AM learner

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

Jun 27, 2011 at 02:47 AM Kev Riley ♦♦

(comments are locked)

	Follow @Ask_SSC
	Follow Ask SSC on Facebook
	Find Ask SSC on Google+
	Find us on LinkedIn

Simulate Row_Number in Sql server 2000 using tally table

Follow this question

Related Questions