Sum child only take X number and ordered by its date property-CodePudding

How do I write a query which lists table a with the sum of the values of a column from table b but only taking the top 10 values from table b ordered descending by the date column? So we only sum the most recent 10 values from the child.

I have this but this will take all values from table b:

SELECT TOP (1000) tablea.name, SUM(tableb.value) AS V
FROM tablea
INNER JOIN tableb
ON tablea.ID = tableb.tableaid
GROUP BY tablea.name

table a

ID    | int (PK)
name  |  varchar(10)

table b

ID       | int (PK)
tableAID | int
value    | float
date     | datetime2(7)

CodePudding user response：

I think you need a query like this

WITH tableb_rn AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY tableaid ORDER BY date DESC) AS rn
    FROM tableb        
)
SELECT 
    tablea.name,
    SUM(tableb_rn.value) AS V
FROM tablea
JOIN tableb_rn ON tablea.id = tableb_rn.tableaid AND tableb_rn.rn <= 10
GROUP BY tablea.name

Here I use a CTE to get row numbers per tableaid ordered descending by date for tableb, join it with tablea summarizing it's values only for rows with a row number not greater than 10 (top 10 rows).