-- Test query declare @StudentsScore table (StudentID int primary key, TestScore int) insert into @StudentsScore select 1, 100 union all select 2, 100 union all select 3, 95 union all select 4, 93 union all select 5, 92 union all select 6, 90 union all select 7, 91 union all select 8, 88 union all select 9, 81 union all select 10, 65 union all select 11, 30 union all select 12, 30 union all select 13, 35 union all select 14, 89 select * from @StudentsScore order by TestScore, StudentID declare @TotalStudents int select @TotalStudents = count(*) from @StudentsScore select avg(TestScore*1.0) from (select TestScore from (select *, row_number() over (order by TestScore, StudentID) as Row from @StudentsScore) X where Row = (@TotalStudents+1)/2 union all select TestScore from (select *, row_number() over (order by TestScore, StudentID) as Row from @StudentsScore) X where Row = (@TotalStudents+2)/2) Y