>OK, this one shouldn't be too difficult....I had to write this in the past week, and figured it would be a good post for a sql puzzle.
>
>Suppose you have a table of test scores....
>
>StudentID TestScore
>1 100
>2 100
>3 95
>4 93
>5 92
>6 90
>7 90
>8 88
>9 81
>10 65
>11 30
>
>The average score is about 84: T-SQL has the AVG function, so that's easy.
>
>But to try to minimize the impact of any outliers, how would you write something to calculate the middle (i.e. median) value?
>
>For those not familiar with the median calcuation, it's the middle score. In this situation (an odd # of rows, sorted by testscore desc), it would be the sixth (middle) row. Had the table contained an even # of rows (e.g. 12 rows), it would be the straight average of rows 6 and 7.
>
>So, how would you write something (fairly generic) to calculate the median score? (No googling, please) <s>

I would give the simplest T-SQL answer using the program logic (not a set based logic)

declare @TotalStudents int

select @TotalStudents = count(*) from StudentsScore

if @TotalStudents%2 = 1
   -- Using integer math logic
    select TestScore from (select *, row_number() over (order by ID)  as Row from StudentsScore) X where Row = @TotalStudents/2 + 1
else
   select avg(TestScore) from (select TestScore from (select *, row_number() 
over (order by ID)  as Row from StudentsScore) X where Row = @TotalStudents/2
    union all TestScore from (select *, row_number() over (order by ID)  as Row from StudentsScore) X 
    where Row = @TotalStudents/2 + 1) Y group by TestScore

This is from the top of my head - not tested.

I may come up with a better answer later. BTW, I added ROW_NUMBER() in case we have gaps in sequence for some reason. If we don't, we can use straight selects based on ID.