Rank with median on ties - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Rank with median on ties

Author

Topic

udayfn12
Starting Member

15 Posts

Posted - 2002-09-18 : 19:29:12

Hi All,

Could some one help me how to find ranks with median when ties exists.

We are using SQL 2000 Server and do not have Analysis Server.

create table ExamResults (Student varchar(10), math int, science int , english int);

insert into ExamResults values ('Lenny',2,2,1);
insert into ExamResults values ('Ralph',3,3,2);
insert into ExamResults values ('Joe',4,4,3);
insert into ExamResults values ('Mary',5,5,4);
insert into ExamResults values ('Frank',5,6,5);
insert into ExamResults values ('Susan',5,7,6);
insert into ExamResults values ('Bill',7,7,7);
insert into ExamResults values ('Ben',0,7,8);
insert into ExamResults values ('Fred',0,8,9);
insert into ExamResults values ('George',10,9,10);

Below query returns Rank

select a.student, (select count(*) + 1 from examresults b where a.math > b.math ) as rank
from examresults a
OutPut
======
student rank
---------- -----------
Lenny 3
Ralph 4
Joe 5
Mary 6 >>> 6+7+8/3 = 7
Frank 6 >>> 6+7+8/3 = 7
Susan 6 >>> 6+7+8/3 = 7
Bill 9
Ben 1 >>> 1+2 / 2 = 1.5
Fred 1 >>> 1+2 / 2 = 1.5
George 10

I want median values for ties shown above as >>> and I want to calculate ranks on all columns.

Thanks,
Reddy.

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-09-19 : 08:01:38

I am not 100% sure this will work in all cases, but try it and let me know if it works correctly. I do return the correct results based on the data you provided.

Select aaa.student, IsNull(bbb.rank+((cast(bbb.num_of_records as float)/2)-.5), aaa.rank) New_Rank
FROM (select a.student, (select count(*) + 1 from examresults b where a.math > b.math ) as rank
from examresults a) aaa LEFT JOIN
(Select aa.rank, count(*) num_of_records
FROM (select a.student, (select count(*) + 1 from examresults b where a.math > b.math ) as rank
from examresults a) aa
GROUP By aa.rank having count(*) > 1) bbb
ON aaa.rank = bbb.rank

Jeremy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources