2

I have a SQL table which contains a list of financial transactions with their corresponding accounts, dates, and closing account balance (balance after transaction is processed).

Date TransactionId ClosingBalance AccountId
2015-06-11 51330001 -48.39 1033938
2015-06-11 51330002 -54.92 1033938
2015-06-11 51330003 -85.15 1033938
2015-06-12 51330004 114.85 1033938
2015-06-12 51330005 113.18 1033938
2015-06-12 51330006 110.55 1033938
2015-06-12 51330007 107.86 1033938
2015-06-12 51330008 98.84 1033938
2015-06-12 51330009 56.51 1033938
2015-06-15 51330010 1284.08 1033941
2015-06-15 51330012 1334.08 1033938
2015-06-15 51330013 1332.41 1033938
2015-06-15 51330015 1329.78 1033938

An account may have multiple transactions on any given day. I'm trying to write a query which will return the last transaction (with the highest transaction number) for each account on each day essentially giving me the ending daily balance for that account.

Date TransactionId ClosingBalance AccountId
2015-06-11 51330003 -85.15 1033938
2015-06-12 51330009 56.51 1033938
2015-06-15 51330010 1284.08 1033941
2015-06-15 51330015 1329.78 1033938
realmvpisme
  • 55
  • 1
  • 6
  • 1
    please share the insert script for this data. It is very difficult to write data scripts for writing query. – Amit Verma Jul 02 '21 at 03:08
  • Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Charlieface Jul 02 '21 at 10:10

1 Answers1

3

Two quick options. Both use with window function row_number() over() and both will create the same results (except for the extra column RN)

First is WITH TIES

Select top 1 with ties * 
 From  YourTable
 Order By row_number() over (partition by AccountID,Date order by [TransactionId] desc)

Second with with a CTE

with cte as (
Select *
      ,RN = row_number() over (partition by AccountID,Date order by [TransactionId] desc)
 From  YourTable
)
Select * from cte where RN=1

WITH TIES looks cleaner and easier but the second option is a nudge more performant

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66