x

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?

more ▼

asked Jun 27, 2011 at 02:27 AM in Default

avatar image

learner
302 17 19 22

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Jun 27, 2011 at 02:40 AM

avatar image

Kev Riley ♦♦
64.2k 48 62 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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x501

asked: Jun 27, 2011 at 02:27 AM

Seen: 1550 times

Last Updated: Jun 27, 2011 at 03:12 AM

Copyright 2016 Redgate Software. Privacy Policy