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

learner gravatar image

learner
302 17 18 20

(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

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x473

asked: Jun 27, 2011 at 02:27 AM

Seen: 1310 times

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