>>>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.
>
>
>I don't know why you would order by Id
>
>But what about this
>

>declare @StudentScore  table (Id int, Score int)
>
>insert into @StudentScore
>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, 90
>union all select 8, 88
>union all select 9, 81
>union all select 10, 65
>union all select 11, 30;
>
>
>
>select avg(Score)  as Median 
>	from (	select	Id, 
>					Score, 
>					ROW_NUMBER() over (order by Score) Rank, 
>					@@ROWCOUNT as Number 
>		from  @StudentScore
>	 ) xxx 
>	where ( Rank in ((Number+1)/2, (Number+2)/2) )
>

Check this interesting blog by Peter Larsson
http://weblogs.sqlteam.com/peterl/archive/2009/09/16/Median-and-weighted-median.aspx