Alternate way of Row Number() function

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

General SQL Server Forums

New to SQL Server Programming

Alternate way of Row Number() function

Author

Topic

john20
Starting Member

30 Posts

Posted - 2008-05-22 : 10:52:29

Hi all,

I AM USING ROW_NUMBER() FUNCTION IN MY QUREY IN SQL 2005 BUT IT IS GIVING ME PROBLEM IN SQL 2000.

SO CAN U TELL ME WHAT IS THE ALTERNATE WAY TO ACHIVE SAME RESULT.
MY QUREY IS:

select row_number() over (order by SalesYTD asc) as rownum, CountryRegionName, FirstName, LastName, SalesYTD, SalesLastYear from Sales.SalesPerson

CAN U TELL ME HOW CAN I REPLACE ROW_NUMBER() FUNCTION.

THANKS IN ADVANCE

-JOHN

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-22 : 10:55:05

There is no easy way to do this due to the possibility of duplicate values.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-22 : 10:57:42

[code]DECLARE @Temp TABLE
(
RowNum INT IDENTITY(1, 1),
CountryRegionName {Datatype here},
FirstName {Datatype here},
LastName {Datatype here},
SalesYTD {Datatype here},
SalesLastYear {Datatype here}
)

INSERT @Temp
SELECT CountryRegionName,
FirstName,
LastName,
SalesYTD,
SalesLastYear
FROM Sales.SalesPerson
ORDER BY SalesYTD

SELECT CountryRegionName,
FirstName,
astName,
SalesYTD,
SalesLastYear
FROM @Temp[/code]

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-26 : 05:19:17

quote:
Originally posted by john20

Hi all,

I AM USING ROW_NUMBER() FUNCTION IN MY QUREY IN SQL 2005 BUT IT IS GIVING ME PROBLEM IN SQL 2000.

SO CAN U TELL ME WHAT IS THE ALTERNATE WAY TO ACHIVE SAME RESULT.
MY QUREY IS:

select row_number() over (order by SalesYTD asc) as rownum, CountryRegionName, FirstName, LastName, SalesYTD, SalesLastYear from Sales.SalesPerson

CAN U TELL ME HOW CAN I REPLACE ROW_NUMBER() FUNCTION.

THANKS IN ADVANCE

-JOHN

If record number is needed only for displaying, consider doing this at front end application

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources