Order by and top on grouped rows

How do I order by date desc my grouped items and get top 20?

For example: The table Orderproduct have OrderID, ProductId, Date, Price, I want to group by ProductId and sort each grouped by Date desc then get top 20 and avg(price).

On LINQ, this is how (but the sql generated is very dirty and the performance is very bad).

OrderProduct
    .GroupBy(g => g.ProductId)
    .Select(s => new{ s.Key, Values = s.OrderByDescending(o => o.Date)开发者_StackOverflow.Take(20) })
    .Select(s => new{ Avg = s.Values.Average(a => a.Price) } )

---

If I understand your question this might work for you.

select ProductId,
       avg(price) as AvgPrice
from ( select ProductId,
              Price,
              row_number() over(partition by ProductId order by [Date] desc) as rn
       from Orderproduct
     ) as O
where rn <= 20
group by ProductId       

---

Based on your comments, this may work:

SELECT  [Date], 
        ProductID, 
        MIN(Price) as Min, 
        MAX(Price) as MAX, 
        AVG(Price) as Avg
FROM OrderProduct o1
WHERE [Date] IN (SELECT TOP 20 [Date] 
                 FROM OrderProduct o2
                 WHERE o1.productid = o2.productid)
GROUP BY [Date], ProductID
ORDER BY [Date] DESC

---

Does this get you want you want for ALL rows? I understand you ONLY want the top 20 for each product. I just don't want to spend the time on top 20 if this is not correct. And does top 20 mean 20 highest prices, or 20 most recent dates, or 20 most recent (can there be more than one per day per productID?)?

    SELECT [ProductID], [Date], [Price]
    FROM [OrderProduct] 
    ORDER BY [ProductID] asc, [Date] desc, [Price] desc 
    COMPUTE AVG(Price) BY [ProductID];