Click here to Skip to main content
Click here to Skip to main content

Ranking Query in Sql Server 2000

By , 14 Mar 2007
Rate this:
Please Sign up or sign in to vote.

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 :

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

License

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.

Comments and Discussions

 
GeneralMy vote of 3 Pinmemberarun potti13-Aug-10 2:05 
GeneralRank with ties as opposed to DENSE_RANK() Pinmemberbobsheep20-Sep-09 23:43 
Generalgrt job..!!! Pinmembermansuri.isteyaq7-Sep-09 23:48 
GeneralExcelente!!! Pinmembergonet22-Apr-08 8:17 
GeneralSql rank Pinmemberkmnhameed31-Aug-07 0:29 
GeneralRe: Sql rank PinmemberMustakim Mansuri31-Aug-07 3:37 
GeneralImportant Articles For ranking Query Pinmemberimcomputerengg31-Mar-07 22:38 
Generalvery nice PinmemberImran Khan Pathan15-Mar-07 20:02 

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

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

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