CODEWITHSUNDEEP
sql-serversql-server-2008
Justin J Stark
Justin J Stark

Reputation: 500

Inconsistent SUM when using OVER PARTITION in MSSQL

On a static table, I'm trying to run the following query in SSMS (to get the latest transaction for each user and sum the dollar value):

SELECT
    SUM(nMoney) As TotalMoney
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY nGroup, nUser ORDER BY dTransaction DESC) AS SEQNUM
    , nMoney
    FROM [MyDB].[dbo].[MyTable]
) MySubquery
WHERE MySubquery.SEQNUM=1

This is a table with 2,701,510 rows and the nMoney column is of type Decimal(12,2). When I run it multiple times, I get a varying result:

2317367341.75
2317370443.45
2317449819.62
2317360649.43
2317449819.62

What could be causing the inconsistent result?

Upvotes: 2

Views: 809

Answers (1)

Martin Smith
Martin Smith

Reputation: 453243

For floating point arithmetic the order that numbers are added in can affect the result.

But in this case you are using Decimal(12,2) which is precise.

The issue is that with duplicate values for nGroup, nUser, dTransaction the ROW_NUMBER is not deterministic so different runs can return different results.

To get deterministic behaviour you can add guaranteed unique column(s) into the end of the ORDER BY to act as a tie breaker.

Upvotes: 5

Related Questions