title: Select Top 1 From a Table For Each Id in another Table
body: <p>Table Structure is:</p>
  
  <pre><code>create table fruit (
    id int identity(1,1),
    name varchar(max)
  )
  
  create table fruit_allocation (
    id int identity(1,1),
    fruit_id int references fruit(id),
    customer_id int references store(id),
    amount float,
  )
  
  create table measurement (
    fruit_allocation_id int references fruit_allocation(id),
    measurement_date datetime,
    measurement float,
  )
  </code></pre>
  
  <p>Each fruit can be allocated to more than one customer creating a fruit_allocation record. Each fruit_allocation record can have multiple measurements.</p>
  
  <p>I want to select the latest measurement for each fruit_allocation given a fruit id</p>
  
  <p>So far I have the following:</p>
  
  <pre><code>select * 
    from measurement 
   where fruit_allocation_id in (select id 
                                   from fruit_allocation 
                                  where fruit_id = 10)
  </code></pre>
  
  <p>This returns all measurements for that fruit, I want to just return 1 measurement per fruit_allocation.</p>