Howto query average of most recent data in sqlserver

Question

I want to find the average of the most recent x results. Is it possible to use an aggregate function on a limited number of results with sql server?

Here is what I have tried and the errors I get:

select avg(top(100) convert(float, AnalysisData))
from tab
order by DatePerformed desc

Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'top'.

select AVG(data)
from (
select top(100) convert(float, AnalysisData) As data
from tab
order by DatePerformed desc
);

Msg 102, Level 15, State 1, Line 7 Incorrect syntax near ')'.

This is sql server 2008

Answer 1

In your second example, you need an alias name for the subquery:

select AVG(data)
from (
    select top 100 convert(float, AnalysisData) As data
    from tab
    order by DatePerformed desc
) Top100Records;

You probably don't need the parenthesis around 100 either.