CODEWITHSUNDEEP
sqlsql-servert-sql
realmvpisme
realmvpisme

Reputation: 55

Select Rows With the Greatest Value In One Column When Another Column Has matching Values

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

Upvotes: 2

Views: 33

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81940

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

Upvotes: 3

Related Questions