# Thread: Rank with median on ties

1. Registered User
Join Date
Sep 2002
Posts
1

## Unanswered: Rank with median on ties

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.

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
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/

3. Registered User
Join Date
Apr 2004
Posts
5

## Do it in Access SQL

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•