>>Yes, that produces the correct result, thanks.
>>
>>I like Greg's (and the link that Eric posted) because it solves it in one SELECT statement. I realize that sometimes an "if one condition, run this select....otherwise, run this select" is necessary, but in this case I think one SELECT is preferable.
>>
>>Thanks for playing, everyone! OK, that was too easy, have to come up with a tougher one :)
>
>You're right, one select works the same way:
>
>

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

Why do you need a union and the next select twice (which can be avoided with a cte I think)

 select TestScore from (select *, row_number() over (order by TestScore, StudentID)  as Row from @StudentsScore) X