locked
Select Top by Group RRS feed

  • Question

  •  

    Hi there.

    I am struggling with a problem where I am trying to select the 3 most recent entries in a table for a each property and average them. I have seen several examples of how to do this with a unique identifier, but my primary key is a composite of PropertyID and Period, The table is such:

    PropertyID varchar(10)
    Period datetime
    ContactValue decimal(12,2)
    ContactCount int

    Example of data:

    ABC123 1/1/2008 25.00 8
    ABC123 10/1/2007 33.33 15
    ABC123 9/1/2007 80.00 5
    ABC123 6/1/2007 50.00 2
    ABC123 5/1/2007 75.00 4
    DEF456 2/1/2008 50.00 8
    DEF456 1/1/2008 95.0 20
    DEF456 12/1/2007 40.00 5
    DEF456 11/1/2007 66.67 3

    What I want is the weighted average for the most recent three (based on Period) for each PropertyID and I cant seem to get the most recent three from a subquery.

    Any ideas would be appreciated.

    Thanks, Mike

    Tuesday, June 24, 2008 4:23 AM

Answers

All replies