Click here to Skip to main content
15,867,568 members
Articles / Database Development / SQL Server
Article

Ranking Query in Sql Server 2000

Rate me:
Please Sign up or sign in to vote.
1.78/5 (8 votes)
14 Mar 2007CPOL2 min read 72.9K   21   8
Sql Query in Sql Server 2000 for getting rank on particular numeric field in table.

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)


Written By
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 Pin
arun potti13-Aug-10 2:05
arun potti13-Aug-10 2:05 
GeneralRank with ties as opposed to DENSE_RANK() Pin
bobsheep20-Sep-09 23:43
bobsheep20-Sep-09 23:43 
Generalgrt job..!!! Pin
mansuri.isteyaq7-Sep-09 23:48
mansuri.isteyaq7-Sep-09 23:48 
GeneralExcelente!!! Pin
gonet22-Apr-08 8:17
gonet22-Apr-08 8:17 
GeneralSql rank Pin
kmnhameed31-Aug-07 0:29
kmnhameed31-Aug-07 0:29 
GeneralRe: Sql rank Pin
Mustakim Mansuri31-Aug-07 3:37
Mustakim Mansuri31-Aug-07 3:37 
Hi,

your requirement can't fulfill with single query. But everything is possible. You can make separate procedure for that or do some coding after getting result.

If you are working with .Net, use this

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]

Select RowNum = (Select Count(1) From rank R2 Where R2.pk_id <= R1.pk_id)
From rank R1

1. Execute Both above queries, First query gives you rank and second one gives you Rownumber.

2. Fill result set of both query in separate DataTable. i.e, RankTable and
RowNumberTable

3. Loop for all record and Put one if condition in loop like if Rank field from RankTable is less than Rownumber field from RowNumberTable then replace Rank with Rownumber.

Hope this may helpful to you.






Mustakim Mansuri

GeneralImportant Articles For ranking Query Pin
imcomputerengg31-Mar-07 22:38
imcomputerengg31-Mar-07 22:38 
Generalvery nice Pin
Imran Khan Pathan15-Mar-07 20:02
Imran 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.