Andrea Masiero Andrea Masiero - 5 months ago 20
MySQL Question

Get records with max sum value of grouped SQL results

I have a table which has three fields like this:

PK account value
-----------------------
1 40010101 130
1 40010101 500
1 40010569 590
2 40010569 300
2 40010101 200


I want to select the record which has highest value of the sum of Value for each PK, grouped by PK and Account, so my result should be like this:

PK account value
-----------------------
1 40010101 630
2 40010569 300


I tried this solution:

SELECT
pn.*
FROM
(select PK, account, sum(value) as value
from table
group by PK, account) pn
INNER JOIN
(select PK, MAX(value) AS maxvalue
from (select PK, account, sum(value) as value
from table
group by PK, account) pn
group by PK) maxsum
ON pn.PK= maxsum.PK
AND pn.value = maxsum.maxvalue


It works, but I ask you a faster solution. I could have thousand of records to match. I can't use a Stored procedure.
Using SQL server.

Answer

In SQL Server

SELECT  PK,
        account, 
        sum_value AS [value]
FROM (
    SELECT  PK,
            account, 
            SUM([value]) as sum_value,
            ROW_NUMBER() OVER (PARTITION BY PK ORDER BY SUM([value]) DESC) as rn
    FROM [table]
    GROUP BY account, PK
) as p
WHERE rn = 1

Output:

PK  account     value
1   40010101    630
2   40010569    300
Comments