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.

maybe you'll find it in Statistics in SQL, Chapter 8 of O'Reilly's Transact-SQL Cookbook -- Means, modes, medians, Standard deviations, Variances, Standard errors, Confidence intervals, Correlations, Moving averages, Weighted moving averages

rudy
http://rudy.ca/

Something like the code below has worked well for me. And the nice thing is that it's all done in SQL.

SELECT LAST(Number)
FROM (SELECT TOP 50 PERCENT Number
FROM tblList
WHERE Number<>''
ORDER BY Number);