SQL Server : select only last record per customer from a join query
<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>