title: SQL Server : select only last record per customer from a join query
body: <p>Assume I have these 3 tables :</p>
  
  <p><a href="https://i.stack.imgur.com/A2ojl.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/A2ojl.png" alt="Customer type 1"></a></p>
  
  <p><a href="https://i.stack.imgur.com/J3iE2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/J3iE2.png" alt="Customer type 2"></a></p>
  
  <p><a href="https://i.stack.imgur.com/gkHi5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/gkHi5.png" alt="enter image description here"></a></p>
  
  <p>The first 2 tables define customers of different types ,i.e second table has other columns which are not included in table 1 i just left them the same to save complexity.</p>
  
  <p>The third table defines orders for both types of customers . Each customer has more than one orders</p>
  
  <p>I want to select the last order for every customer, i.e the order with <code>order_id</code> 4 for <code>customer</code> 1 which was created on 23/12/2016 and the order with <code>order_id</code> 5 for <code>customer</code> 2 which was created on 26/12/2016 </p>
  
  <p>I tried something like this : </p>
  
  <pre><code>select * 
  from customertype1 
  left join order on order.customer_id = customertype1.customer_id 
  order by order_id desc;
  </code></pre>
  
  <p>But this gives me multiple records for every customer, as I have stated above I want only the last order for every <code>customertype1</code>. </p>