title: How to use Django ORM in a join with an aggregate subquery
body: <p>I have a model like this (simplified):</p>
  <pre><code>class Events(models.Model):
      reference_date = models.DateField()
      event = models.IntegerField(db_index=True)
      created_at = models.DateTimeField()
      updated_at = models.DateTimeField()
  
      class Meta:
          unique_together = (('reference_date', 'event'),)
  </code></pre>
  <p>I can have the same <code>event</code> with multiple reference dates, but I only have one <code>event</code> per <code>reference_date</code>. The table data looks something like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>reference_date</th>
  <th>event</th>
  <th>created_at</th>
  <th>updated_at</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>2022-01-01</td>
  <td>12345</td>
  <td>2022-03-05 18:18:03</td>
  <td>2022-03-06 18:12:09</td>
  </tr>
  <tr>
  <td>2</td>
  <td>2022-01-02</td>
  <td>12345</td>
  <td>2022-03-08 08:05:11</td>
  <td>2022-03-08 08:05:55</td>
  </tr>
  <tr>
  <td>3</td>
  <td>2022-01-08</td>
  <td>12345</td>
  <td>2022-06-15 18:18:12</td>
  <td>2022-06-16 02:23:11</td>
  </tr>
  <tr>
  <td>4</td>
  <td>2022-01-01</td>
  <td>98765</td>
  <td>2022-01-11 07:55:25</td>
  <td>2022-01-13 08:45:12</td>
  </tr>
  <tr>
  <td>5</td>
  <td>2022-01-02</td>
  <td>98765</td>
  <td>2022-06-22 10:25:08</td>
  <td>2022-07-05 18:55:08</td>
  </tr>
  <tr>
  <td>6</td>
  <td>2022-01-09</td>
  <td>45678</td>
  <td>2022-02-19 12:55:07</td>
  <td>2022-04-16 12:21:05</td>
  </tr>
  <tr>
  <td>7</td>
  <td>2022-01-10</td>
  <td>45678</td>
  <td>2022-03-05 11:23:45</td>
  <td>2022-03-05 18:55:03</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I need the latest record for each <code>event</code>. But I need all the <code>event</code> attributes, not just the <code>max(reference_date)</code></p>
  <p>I'm looking for this result:</p>
  <pre><code>[
  {'id': 3, 'event': 12345, 'reference_date': '2022-01-08', 'created_at': '2022-06-15 18:18:12', 'updated_at': '2022-06-16 02:23:11'},
  {'id': 5, 'event': 98765, 'reference_date': '2022-01-02', 'created_at': '2022-06-22 10:25:08', 'updated_at': '2022-07-05 18:55:08'},
  {'id': 7, 'event': 45678, 'reference_date': '2022-01-10', 'created_at': '2022-03-05 11:23:45', 'updated_at': '2022-03-05 18:55:03'}
  ]
  </code></pre>
  <p>From a 'sql-perspective' I could get the results in multiple ways: SUBQUERIES, ROW_NUMBER, CORRELATED SUBQUERY etc. In this particular case for clarity reasons I prefer to use a join with itself using an aggregate inside the subquery.</p>
  <p>If I'd write a raw query, I'd would do like this:</p>
  <pre><code>SELECT
   e.*
  FROM events as e
  INNER JOIN (
          SELECT
           event,
           max(reference_date) as reference_date
          FROM events
          GROUP BY event) AS b
  ON b.reference_date = e.reference_date AND b.event = e.event
  </code></pre>
  <p>I think this query is very straightforward and has a good performance for the data volume in this table (hundreds of thousands of records)</p>
  <p>I looked for several ways to build this type of query (Subquery, PrefetchRelated etc) but I couldn't find any proper way to translate this query into Django ORM syntax.</p>
  <p>Is there a way using the 'django-orm way' without a convoluted ORM solution with the same reasonable performance?</p>
  <p>ps: I need this query (or similar ones) for different database engines (PostgreSql, MySQL, MSQLServer ..)</p>