[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ROW_NUMBER in SQL2000

Posted on 2009-04-23
5
Medium Priority
?
757 Views
Last Modified: 2012-05-06
hi

i am using following row number function in my sql server 2005 :

SELECT @MaxStoreId + ROW_NUMBER() OVER (ORDER BY @MaxStoreId)

but now i want the same result in sql server 2000
but this row number function not supported in sql 2000
so plz give me any alternate solution for this....

Thank you,

Regards,
KJ.
0
Comment
Question by:kapiljoshi
  • 2
4 Comments
 
LVL 7

Accepted Solution

by:
Alpha Au earned 600 total points
ID: 24213344
the sad thing is sql 2000 don't have this feature,

you will need a table variable / temp table with identity column.


create table #temp(
rowID [int] IDENTITY (1, 1),
MaxStoreID int
)
 
insert into #temp
    select MaxStoreID from sthTable order by sthrow
 
select rowID, MaxStoreID from #temp

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24213461
piece of advice: above code will reduce performance like anything in live environment with big table, If it is possible, alter your table and insert identity column.
0
 
LVL 23

Expert Comment

by:adathelad
ID: 24213590
An alternative to use a temporary table is something like this:

SELECT t1.MaxStoreID,
    (SELECT COUNT(1) FROM YourTable t2 WHERE t2.MaxStoreID <= t1.MaxStoreID) AS RowId
FROM YourTable t1
ORDER BY t1.MaxStoreID

0
 
LVL 7

Expert Comment

by:Alpha Au
ID: 24215668
Yup, i agree using a temp table would be a loading to sqlserver (if with large data size),
but this would be a small change comparing to altering table structure

you may also use table variable instead, which should have better performance.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question