Ranking Query in Sql Server 2000

Introduction

Sql Query in Sql Server 2000 for getting rank on particular numeric field in table. Currently in Sql server 2000 there are two ways for getting Rank

(1) Rank Function : Rank((c,d), {(a,b), (e,f), (c,d)}) but it is not sufficient to require result set.

(2)SQL Server 2000 FullText Search Service , this is for Text and not give proper result of Rank.

Following query based solution for getting result for Rank on numeric field in Table.

Using the code

SQL Server 2000 does not give any built in functionality for ranking function on numeric field of table. That is now available in SQL server 2005.

If we want rank on particular numeric field, we can fulfill our requirement by two ways:

(1) With Programming Language. By For Loop , While Loop. Or Add another field in table specially for Ranking.

(2) With Sql Query.

To get Rank with Sql query is more efficient then other solutions.

E.g. One application is going on for online contest. Every user get points. Now we want top ten users list. In list it may be possible that two or more users with same point. So we have to allocate same rank for all users who have same points. In final result set for top 10 users, total result is 10 or may be more than 10.

Suppose table contain 3 fields :

And data in this table

Now as requirement , we want list of users name with highest point with his rank Output result look like following table. Here user name 'pqr' has highest point so he is at first rank and user name 'abc' and 'srk' have same points with same ranking.

Here is Query for getting this type of result

-- Create one table

CREATE TABLE [dbo].[rank] (
    [pk_id] [int] IDENTITY (1, 1) NOT NULL ,
    [name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [point] [int] NOT NULL 
) ON [PRIMARY]
GO

-- Insert some value in it.

insert into rank (name, point) values ('abc',250)
insert into rank (name, point) values ('xyz',150)
insert into rank (name, point) values ('pqr',350)
insert into rank (name, point) values ('stu',200)
insert into rank (name, point) values ('vwx',270)
insert into rank (name, point) values ('srk',250)

-- Apply this query to get rank

select [R1].[pk_id],[R1].[name], [R1].[point], rank = (select count(distinct [R2].[point]) from [rank] [R2] where [R1].[point] <= [R2].[Point]) from [rank] [R1]
order by [rank]

-- Apply this query to get Top 5 rank

select Top 5 [R1].[pk_id],[R1].[name], [R1].[point], rank = (select count(distinct [R2].[point]) from [rank] [R2] where [R1].[point] <= [R2].[Point]) from [rank] [R1]
order by [rank]

-- Get rank of  single user e.g. 'srk'

select [R1].[pk_id],[R1].[name], [R1].[point], rank = (select count(distinct [R2].[point]) from [rank] [R2] where [R1].[point] <= [R2].[Point]) from [rank] [R1]
where name = 'srk' order by [rank]