I want to find the last transaction for each account prior to a certain date

Question

I have a table which is defined (on Azure SQL Server) as follows:

CREATE TABLE dbo.[transaction] 
(
    id INT IDENTITY(1,1) NOT NULL,
    [date] DATETIME NULL,
    amount FLOAT NULL,
    balance FLOAT NULL,
    account_id INT NULL,
    CONSTRAINT PK__transact__32F PRIMARY KEY (id)
)

I want to find the last balance for each account prior to a certain date. The columns I need returned are: account_id, date, balance.

I've tried:

select account_id, max(date) as date 
from dbo.[transaction] 
group by account_id

This works, but it does not return the balance.

Secondly, my transactions are ordered first by date, and then by id. So if multiple transactions occur on the max date, it should choose the balance on the transaction with the highest id.

My application is written in flask-sqlalchemy, so a sqlalchemy answer would be great, but I'd also be happy with an answer in SQL.

Answer 1

You could use the row_number windows function to number the rows per account id, and take the last one per account:

SELECT account_id, [date], balance
FROM   (SELECT account_id, [date], balance,
               ROW_NUMBER() OVER (PARTITION BY account_id
                                  ORDER BY [date] DESC, id DESC) AS rn
        FROM   [transaction]) t
WHERE  rn = 1