Results 1 to 3 of 3
  1. #1
    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. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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. #3
    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
  •