How to get rank?

I would like to write a query that gives me the values of a set of
observations, and their rank.

CREATE TABLE #Values(val int)
INSERT #Values SELECT 1
INSERT #Values SELECT 5
INSERT #Values SELECT 10

I would like to select this:

1 10 -- rank 1, value 10
2 5
3 1

I can put them into a temp table with an identity column, ordered by
the column I'm interested in, and then retrieve in order by the
identity column. I'm wondering if there's a way to do that with a
subquery.

Thanks,
JimRunning count is one way

CREATE TABLE #Values(val int)
INSERT #Values SELECT 1
INSERT #Values SELECT 5
INSERT #Values SELECT 10

select (select count(*) from #Values v where val <= v2.Val) as Rank,*
from #Values v2
order by 2

Denis the SQL Menace
http://sqlservercode.blogspot.com/

jim_geiss...@.countrywide.com wrote:
> I would like to write a query that gives me the values of a set of
> observations, and their rank.
> CREATE TABLE #Values(val int)
> INSERT #Values SELECT 1
> INSERT #Values SELECT 5
> INSERT #Values SELECT 10
> I would like to select this:
> 1 10 -- rank 1, value 10
> 2 5
> 3 1
> I can put them into a temp table with an identity column, ordered by
> the column I'm interested in, and then retrieve in order by the
> identity column. I'm wondering if there's a way to do that with a
> subquery.
> Thanks,
> Jim|||Jim,

if you are using SS 2005 , use row_number() or rank() OLAP function|||Where do you want to show data?
If you use front end application, do Ranking there

Madhivanan


jim_geissman@.countrywide.com wrote:

Quote:

Originally Posted by

> I would like to write a query that gives me the values of a set of
> observations, and their rank.
> CREATE TABLE #Values(val int)
> INSERT #Values SELECT 1
> INSERT #Values SELECT 5
> INSERT #Values SELECT 10
> I would like to select this:
> 1 10 -- rank 1, value 10
> 2 5
> 3 1
> I can put them into a temp table with an identity column, ordered by
> the column I'm interested in, and then retrieve in order by the
> identity column. I'm wondering if there's a way to do that with a
> subquery.
> Thanks,
> Jim

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