how to get rank column in sql 2000

Hi,All,

I have one table like this

UserID,Name,GameScore

1 A 25

2 B 23

3 C 22

4 D 25

5 E 23

6 F 26

Now i want the query which return like this

Name Score Rank

F 26 1

A 25 2

D 25 2

B 23 3

E 23 3

C 22 4

Can anyone give me the sql 2000 query for this

Hisvmali,

Probably we can't get Rank in Sql 2000 , by any of the functions provided

we can achieve this in Sql Server 2005.

Regards

Suresh Kumar G

|||

you can't get rank directly.

but you can try to using cursor,loop and temp table

example:

CREATE PROCEDURE USP_GetRank

AS

declare @.Rank int
set @.Rank = 0

declare @.UserID int
set @.userid = 0

declare @.GameScore int
set @.GameScore = 0

declare @.Name varchar(20)
set @.Name = ''

create TABLE #Tbl_Ranklist(UserID int,Name varchar(100),GameScore int, Rank int)


BEGIN
DECLARE c1 CURSOR FOR
SELECT UserID,Name,GameScore from yourtable order by GameScore desc

OPEN c1

FETCH NEXT FROM c1

INTO @.UserID,@.Name,@.GameScore

WHILE @.@.FETCH_STATUS = 0
BEGIN

set @.Rank=@.Rank+1

Insert INTO #Tbl_Ranklist values (@.UserID,@.Name,@.GameScore,@.Rank)


FETCH NEXT FROM c1
INTO @.UserID,@.Name,@.GameScore
END
CLOSE c1
DEALLOCATE c1
END

SELECT * FROM #Tbl_Ranklist

|||

Hi,

Thanks for the reply.

In your query it is giving different rank values but i want the same rank value for the same score.

|||

try this:

createtable #test(userIDint,Namevarchar(10), Scoreint)

insertinto #test

values(1,'A',25)

insertinto #test

values(2,'B',23)

insertinto #test

values(3,'C',22)

insertinto #test

values(4,'D',25)

insertinto #test

values(5,'E',23)

insertinto #test

values(6,'F',26)

insertinto #test

values(7,'EF',20)

selectidentity(int,1,1)[rank],score rankscore

into #ranksfrom #test

groupby score

orderby ScoreDesc

selectName, score, [rank]

from #test

leftjoin #ranks

on rankscore= score

orderby [rank]

droptable #test

droptable #ranks