title: SQL SERVER: View to get minimum and maximum values from a table
body: <p>I have a MSSQL Server table like this:</p>
  
  <pre><code>id (auto-increment)
  amount
  date
  account_id
  </code></pre>
  
  <p>Data are inserted throughout the day. I now need a view to get the opening and closing amounts for each account for each day. 
  My trouble is creating a fast query to access both minimum and maximum values. 
  Creating a view to access just the minimum is fine using an in statement, however getting both minimum and maximum is tricky. I've tried using a with clause, but the query is incredibly slow.</p>
  
  <p>BTW I am mapping the view to hibernate, so stored procedures and functions won't work the same way (that I know of). </p>
  
  <p><strong>Update</strong> 
  I guess my question wasn't clear from the responses I received. I want to get the opening and closing balances for each account. Maximum and minimum referred to getting the max and min (id) when grouped by date and account_id. 
  I want to get the amount when the id is equal to the maximum id (closing balance) and the amount when the id is equal to the minimum id (opening balance) for each account for each day. </p>