Get records with max sum value of grouped SQL results

Question

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 1

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