title: SQL aggregate function query selecting multiple columns
body: <p>I am trying to query the table below, which is a very basic version of my actual table,</p>
  
  <pre><code>ID   SaleDate     Amount
  1   2014-09-01     50
  1   2014-10-01     20
  1   2014-11-01     10
  2   2013-11-01     10
  2   2013-12-01     20
  2   2014-01-01     25
  </code></pre>
  
  <p>to get an output like</p>
  
  <p><strong>Output</strong></p>
  
  <pre><code>ID   MinDate       MaxDate    StartAmount  EndAmount
  1   2014-09-01   2014-11-01     50           10
  2   2013-11-01   2014-01-01     10           25
  </code></pre>
  
  <p>The SaleDate is always increasing. But the amount may vary. I picked up the min and max dates for each ID. That is the easy part. But, I also want the amount that is on the SaleDate. </p>
  
  <p>I tried using this</p>
  
  <pre><code>Select x.ID,min(x.SaleDate) MinDate, x.Amount StartAmount, max(y.SaleDate) MaxDate,y.Amount EndAmount
  From Sales x Join Sales y
  On x.ID = y.ID
  Group By x.ID,y.ID,x.Amount,y.Amount
  Order By x.ID
  </code></pre>
  
  <p>But I am sure I should not be using the Amount field in the group by function. 
  Is there a similar question in SO that answers this type of situation ? Or if there is an easy solution just point me that way. I don't need the direct answer just a general method to deal with such cases.</p>