如何从具有相同日期时间的一组记录中选择最新记录?

Question

所以我有一些记录，我试图在图表中显示。目前，如果某些记录具有相同的日期时间，那么它们就会分组到图表中，这就是我想要的，但是图表显示了这些记录的总和而不是最后一条记录，但我不确定如何实现这一点。

SUM(TAB.Amount) OVER(PARTITION BY TAB.AccountID order by TAB.AccountID, TransactionValueDate, StatementNumber, SequenceNumber, TAB.RecordID) AS [IntraDayBalance]

这是我用来获取这些记录的代码，它在表中完美运行，但我不能在图表中使用它，就像我说的那样，它对记录求和。那么有没有办法从此列中获取具有相同日期时间的最后一条记录。

不确定如何格式化，但这是一个示例。假设我有 3 条记录(532255.20、2199722.65 和 1995722.65)，它们的日期时间完全相同 (16/04/2019 06：41：04)。Currently at this datetime point the graph shows me the summed figure of these records (4727700.5‬0) but what I actually want it to show is the last record (1995722.65).

那么我要问的是，我将如何显示最后一条记录而不是总和金额。请记住，我无法更改上面显示的代码行，因为它非常适合其原始用途，但不适用于图形，因此图形需要另一行，我只是不知道如何自己做。任何帮助将不胜感激！

编辑：感谢您的帮助。我现在感觉自己像个白痴，但我无法将这些语句中的任何一个合并到我的代码中。我对 T-SQL 仍然很陌生，仍在学习如何正确格式化代码以及事情的发展方向，但这是我的 SELECT 语句，有关如何将这些语句中的任何一个合并到此代码中的任何一个的任何提示都将非常有帮助！

,TAB.AccountID
,TAB.Amount
,AC.ShortName
,FX.FXRate
,FX.IsDivide
,CASE 
WHEN FXRate IS NOT NULL AND FXRate <> 0 THEN
CASE
WHEN FX.IsDivide = 0 THEN
ROUND(TAB.Amount / FXRate,2)
ELSE
ROUND(TAB.Amount * FXRate,2)
END
ELSE
0
END AS BalanceUSD
,ITS.LimitAmount
,ITS.FloorAmount
,CR.SwiftCode AS Ccy
,TAB.RecordType
,AC.AccountNumber
,AC.BankFileIdentifier AS Accountidentifier
,DV.ShortName AS Division
,CP.ShortName AS Counterparty
,TAB.Amount
,SUM(TAB.Amount) OVER(PARTITION BY TAB.AccountID  order by TAB.AccountID, TransactionValueDate, StatementNumber, SequenceNumber, TAB.RecordID) AS [IntraDayBalance]
,CAST(29218 + TransactionValueDate AS DATETIME) AS TransactionValueDate
,CAST(29218 + TransactionEntryDate AS DATETIME) AS TransactionEntryDate
,CASE
WHEN TransactionFlow = 0 THEN
TransactionAmount
ELSE
-TransactionAmount
END AS TransactionAmount
,CASE 
WHEN ITS.TransactionFlow = 0 THEN
ITS.TransactionAmount
ELSE
0
END AS Inflow
,CASE 
WHEN ITS.TransactionFlow = 1 THEN
ITS.TransactionAmount
ELSE
0
END AS Outflow
,TransactionFlow
,TransactionCode
,CustomerReference
,BankReference
,Right(RTRIM('0000000000' + CAST(StatementNumber AS CHAR (20))), 10) AS StatementNumber
,RTRIM(CAST(ITS.SequenceNumber AS CHAR (20))) AS SequenceNumber
,CAST(29218 + StatementDate AS DATETIME) AS StatementDate
--,DATEADD(SECOND,ImportedByTime,CAST(29218 + StatementDate AS DATETIME))  AS StatementDateTime
,CASE
WHEN TAB.RecordType = 'B' THEN
@StartDate
ELSE
DATEADD(SECOND,ImportedByTime,CAST(29218 + StatementDate AS DATETIME))  
END AS StatementDateTime
,InformationToAccountOwner
,Information1
,Information2
,Information3
,Information4
,Information5
,Information6
,ImportedBy
,CAST(29218 + ImportedByDate AS DATETIME) AS ImportedByDate
,CASE
WHEN TAB.RecordType = 'B' THEN
@StartDate
ELSE
DATEADD(SECOND,ImportedByTime,CAST(29218 + ImportedByDate AS DATETIME))  
END AS ImportDateTime
,@StartDate AS StartDate
,@EndDate AS EndDate
,ITS.DebitAmount
,ITS.CreditAmount
,ITS.FloorAmount
,ITS.LimitAmount
,ITS.RecordID

FROM CTE_TransactionsAndBalances AS TAB
LEFT OUTER JOIN
InterimTransactionStatement AS ITS ON TAB.RecordID = ITS.RecordID
LEFT OUTER JOIN
Accounts AS AC ON TAB.AccountID = AC.ACRecordID
LEFT OUTER JOIN
Currencies AS CR ON AC.CurrencyID = CR.CRRecordID
LEFT OUTER JOIN
Divisions AS DV ON AC.DivisionID = DV.DVRecordID
LEFT OUTER JOIN
Counterparties AS CP ON AC.CounterpartyID = CP.CPRecordID
LEFT OUTER JOIN
@tbl_FXRates AS FX ON AC.CurrencyID = FX.FromCcyID
AND FXRateDateN >= FX.StartDateN
AND FXRateDateN <= FX.EndDateN
AND FX.ToCcyID = @ReportingCcyID
--WHERE AC.ACRecordID = 94
order by TAB.AccountID, TransactionValueDate, StatementNumber, SequenceNumber, TAB.RecordID ```

Answer 1

使用子查询和row_numberr()

select * from ( select
SUM(TAB.Amount) OVER(PARTITION BY TAB.AccountID  order by TAB.AccountID, TransactionValueDate, StatementNumber, SequenceNumber, TAB.RecordID) AS [IntraDayBalance],
row_number() over(partition by TAB.AccountID order by TransactionValueDate desc) rn
from table
) a where a.rn=1