title: I want to find the last transaction for each account prior to a certain date
body: <p>I have a table which is defined (on Azure SQL Server) as follows:</p>
  
  <pre><code>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)
  )
  </code></pre>
  
  <p>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.</p>
  
  <p>I've tried: </p>
  
  <pre><code>select account_id, max(date) as date 
  from dbo.[transaction] 
  group by account_id
  </code></pre>
  
  <p>This works, but it does not return the balance.</p>
  
  <p>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. </p>
  
  <p>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.</p>