kapiljoshi
asked on
ROW_NUMBER in SQL2000
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
RiteshShah
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.
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
SELECT t1.MaxStoreID,
(SELECT COUNT(1) FROM YourTable t2 WHERE t2.MaxStoreID <= t1.MaxStoreID) AS RowId
FROM YourTable t1
ORDER BY t1.MaxStoreID
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.
but this would be a small change comparing to altering table structure
you may also use table variable instead, which should have better performance.