Ok, I made this too easy. What I really want is a 5 and 8 period moving average but created a small subset of test data. In the past I was just applying a WHERE condition with a fixed date. However, that does not work as some groups have not been updated in a long time.
Let's say using the test data that I want a 3 period moving average using the last 3 values for each group.
>>I need to retrieve the top 2 most current rows (using date) for each group (A, B, C). I have created some sample data and the expected results. Anyone got a solution?
>>
>Try
>SELECT group, date ;
> FROM temp t1 ;
> WHERE date >= ( ;
> SELECT MAX(date) FROM temp t2 ;
> WHERE t2.group = t1.group ;
> AND date < (SELECT MAX(date) FROM temp t3 ;
> WHERE t3.group = t2.group)) ;
>UNION ;
>SELECT group, date ;
> FROM temp t1 ;
> WHERE date >= ( ;
> SELECT MAX(date) FROM temp t2 ;
> WHERE t2.group = t1.group )
>