Results 1 to 7 of 7
  1. #1
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313

    Unanswered: Find 90th percentile scores for each student

    I am stumped on a set-based approach for this one.

    A cursor approach is straightforward enough, but i want to avoid that.

    Here's my table:

    Code:
    create table StudentScores
    (
        id int primary key identity(1,1),
        student_id int not null,
        score int not null
    )
    with some sample data:

    Code:
    insert into StudentScores (student_id, score) 
    select 1, 10 union all
    select 1, 29 union all
    select 1, 50 union all
    select 1, 53 union all
    select 1, 45 union all
    select 1, 10 union all
    select 1, 29 union all
    select 1, 50 union all
    select 1, 53 union all
    select 1, 45 union all
    select 1, 88 union all
    select 2, 23 union all
    select 2, 54 union all
    select 2, 55 union all
    select 2, 34 union all
    select 2, 56 union all
    select 2, 78 union all
    select 2, 23 union all
    select 2, 54 union all
    select 2, 55 union all
    select 2, 34 union all
    select 2, 56 union all
    select 2, 78 union all
    select 2, 23 union all
    select 2, 54 union all
    select 2, 55 union all
    select 2, 34 union all
    select 2, 56 union all
    select 2, 78 union all
    select 2, 98
    What I want is, for each student, what is their 90th percentile score?

    For a given single student, one possibility would be:

    Code:
    declare @studentid int
    set @studentid = 2
    select top 1 @studentid as student_id, a.score as [90th percentile score]
    from 
    (
        select top 90 percent score from StudentScores 
        where student_id = @studentid order by score asc
    ) as a
    order by a.score desc
    But I want this for all students, and not use a cursor.

    Any ideas?

    Thanks!

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    So ... do you want a single maximum score fro each student that is in the top 90th percentile, or all scores that are in the 90th percentile?

    If you derive a table of 90th percentile points, then maybe the outer query can bring back the student_id and max score.

    Not perfect, but maybe a start:

    Code:
     
    select StudentScores.student_id,  max(StudentScores.score) score, s.[90th percentile score] 
    from StudentScores 
       inner join (
        select student_id, (sum(score) * 9) / 100 [90th percentile score]
        from StudentScores
        group by student_id) s ON s.Student_id = StudentScores.student_id
    where StudentScores.score > s.[90th percentile score]
    group by StudentScores.student_id, StudentScores.score, s.[90th percentile score]
    order by StudentScores.student_id desc

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    What I want is this:

    1. just to be concrete, say each student has 100 scores.
    2. for each student, order the student's scores from highest to lowest.
    3. pick the 10th score down the list for each student.

    If each student had 200 scores, you would pick the 20th score down the list. That's what I mean by 90th percentile score.

    Does that make sense?

    tomh53 - It's not immediately clear to me that your query produces this. I'll study it more and see.

  4. #4
    Join Date
    Sep 2006
    Posts
    6
    Perhaps something like this:

    Code:
    select distinct student_id, score
    from StudentScores ss
    where ss.score = (
    
    select min(score) from StudentScores ss2 where ss2.studenti_id = ss.studentid and score in (
    select top 11 percent score from StudentScores where student_id = ss2.studentid
    )
    )
    I guess that top 11 percent would give the the 90th percent as the min of the group, or does that not work?

    R

  5. #5
    Join Date
    Nov 2003
    Posts
    167
    The article linked below sheds some light on percentiles in tsql:

    http://www.sqlteam.com/item.asp?ItemID=16480


    Good luck.
    Kit Lemmonds

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    lytri,

    your solution worked, except you forgot to add an order by clause in the innermost query. Other than that little change it's perfect.

    thanks!

  7. #7
    Join Date
    Sep 2006
    Posts
    6
    Ahh yes, that would probably help wouldn't it...

    Well, glad I could take a break from work and help someone out

Posting Permissions

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