title: Django ORM: Get latest record for distinct field
body: <p>I'm having loads of trouble translating some SQL into Django. </p>
  
  <p>Imagine we have some cars, each with a unique VIN, and we record the dates that they are in the shop with some other data. (Please ignore the reason one might structure the data this way. It's specifically for this question.  :-)  )</p>
  
  <pre><code>class ShopVisit(models.Model):
      vin = models.CharField(...)
      date_in_shop = models.DateField(...)
      mileage = models.DecimalField(...)
      boolfield = models.BooleanField(...)
  </code></pre>
  
  <p><strong>We want a single query to return a Queryset with the most recent record for each <code>vin</code> and update it!</strong></p>
  
  <pre><code>special_vins = [...]
  
  # Doesn't work
  ShopVisit.objects.filter(vin__in=special_vins).annotate(max_date=Max('date_in_shop').filter(date_in_shop=F('max_date')).update(boolfield=True)
  
  # Distinct doesn't work with update
  ShopVisit.objects.filter(vin__in=special_vins).order_by('vin', '-date_in_shop).distinct('vin').update(boolfield=True)
  </code></pre>
  
  <p>Yes, I could iterate over a queryset. But that's not very efficient and it takes a long time when I'm dealing with around 2M records. The SQL that could do this is below (I think!):</p>
  
  <pre><code>SELECT *
  FROM cars
  INNER JOIN (
     SELECT MAX(dateInShop) as maxtime, vin
     FROM cars
     GROUP BY vin
  ) AS latest_record ON (cars.dateInShop= maxtime)
     AND (latest_record.vin = cars.vin)
  </code></pre>
  
  <p>So how can I make this happen with Django?</p>