Click here to Skip to main content
12,896,806 members (53,714 online)
Click here to Skip to main content
Add your own
alternative version


21 bookmarked
Posted 14 Mar 2007

Ranking Query in Sql Server 2000

, 14 Mar 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
Sql Query in Sql Server 2000 for getting rank on particular numeric field in table.


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 :

Field Name Type
pk_ID int
Name varchar
point int

And data in this table

pk_ID Name Point
1 abc 250
2 xyz 150
3 pqr 350
4 stu 200
5 vwx 270
6 srk 250

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.

pk_ID Name Point Rank
3 pqr 350 1
5 vwx 270 2
6 srk 250 3
1 abc 250 3
4 stu 200 4
2 xyz 150 5

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 

-- 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] 


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Mustakim Mansuri
Web Developer
India India
Web Developer working in leading IT Company in Gujarat, India. I like to work with Microsoft Technologies, specially with ASP.NET (c#) and SQL server 2000.

Happy Coding.

You may also be interested in...

Comments and Discussions

GeneralMy vote of 3 Pin
arun potti13-Aug-10 2:05
memberarun potti13-Aug-10 2:05 
GeneralRank with ties as opposed to DENSE_RANK() Pin
bobsheep20-Sep-09 23:43
memberbobsheep20-Sep-09 23:43 
Generalgrt job..!!! Pin
mansuri.isteyaq7-Sep-09 23:48
membermansuri.isteyaq7-Sep-09 23:48 
GeneralExcelente!!! Pin
gonet22-Apr-08 8:17
membergonet22-Apr-08 8:17 
GeneralSql rank Pin
kmnhameed31-Aug-07 0:29
memberkmnhameed31-Aug-07 0:29 
GeneralRe: Sql rank Pin
Mustakim Mansuri31-Aug-07 3:37
memberMustakim Mansuri31-Aug-07 3:37 
GeneralImportant Articles For ranking Query Pin
imcomputerengg31-Mar-07 22:38
memberimcomputerengg31-Mar-07 22:38 
Generalvery nice Pin
Imran Khan Pathan15-Mar-07 20:02
memberImran Khan Pathan15-Mar-07 20:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170424.1 | Last Updated 15 Mar 2007
Article Copyright 2007 by Mustakim Mansuri
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid