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 '11 at 02:40 AM

Kev Riley ♦♦
46.1k ● 38 ● 43 ● 69

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 '11 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 '11 at 02:47 AM Kev Riley ♦♦

(comments are locked)

Simulate Row_Number in Sql server 2000 using tally table

Follow this question

Related Questions