Equalent to ROW_NUMBER() in SQL 2000? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Equalent to ROW_NUMBER() in SQL 2000?

Author

Topic

somu_p
Starting Member

13 Posts

Posted - 2008-04-07 : 04:27:11

Hi,

I have used ROW_NUMBER() ranking function in a procedure (SQL 2005) for filtering purpose.

Now I want to do the same functionality in SQL 2000.

How to do this?

Thanks
Somu

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-07 : 04:43:57

The closest match in SQL server 2000 for ROW_NUMBER() is using an IDENTITY column.

Also see this: http://vyaskn.tripod.com/programming_faq.htm#q17

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-04-07 : 11:04:40

Plug'n'play example:

declare @table table (id int, firstname varchar(30))

insert into @table
select 1, 'John' union all
select 2, 'Mary' union all
select 4, 'Bill' union all
select 7, 'James' union all
select 11, 'Chris'

select 
	a.*, 
	(select count(*) as counter from @table where id <= a.id) as Rank
from @table a

--
Lumbago
"SELECT Rum, Coke, Lime, Ice FROM bar WHERE ClosingTime = 'Late' AND FemaleMaleRatio > 4"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-07 : 11:16:38

Although that's painfully slow for large volume of data.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-04-07 : 11:22:11

Unfortunately yes...but as far as I know it's the only way...

--
Lumbago

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-07 : 11:29:03

That's why I recommended using IDENTITY column instead.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-04-07 : 11:38:10

I'm confused...how can you use an identity-column to solve this issue? It will be totally useless once you add a where-clause or delete a row from the table...? I guess the only way would be to insert into a temp-table/table-variable with an identity-column but I can't see that this would be any cheaper...

--
Lumbago

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-08 : 03:11:18

quote:
Originally posted by Lumbago

Unfortunately yes...but as far as I know it's the only way...

--
Lumbago

as long as the table has unique key

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources