I've got a table that looks something like this (I've omitted some columns):
| ID | Key_ID | Project_ID | other columns |
|---|---|---|---|
| 1 | 1 | 123456 | a |
| 2 | 1 | 123456 | b |
| 3 | 2 | 123456 | c |
| 4 | 2 | 123456 | d |
| 5 | 3 | 654321 | e |
| 6 | 3 | 654321 | f |
| 7 | 4 | 654321 | g |
| 8 | 4 | 654321 | h |
Using Django ORM, I would need to get the equivalent of this query:
SELECT * FROM table AS t
INNER JOIN (
SELECT MAX(ID) AS max_ID FROM table
GROUP BY Key_ID
WHERE Proeject_ID = 123456
) AS sub_query
ON t.ID = sub_query.max_ID
I've tried some aggregate and annotate combinations but I don't seem to be able to achieve the GROUP BY in the subquery. If I could do that I could then try to use a .filter(id__in=<subuery_result> so effectively use a SELECT ... WHERE ID IN <subquery_result although the INNER JOIN would be ideal as the subquery result could be quite large.
UPDATE:
The database I use is PostgreSQL and the accepted answer only works with this.
This is the actual model:
class SystemKey(models.Model):
# The ID (primary key) is handled by Django.
key_id = models.PositiveIntegerField(
help_text="Unique key ID from System."
)
project = models.ForeignKey(
"core.SystemProject",
on_delete=models.PROTECT,
help_text="System project that this key belongs to.",
)
# There are a whole bunch of other properties here
record_created = models.DateTimeField(
auto_now_add=True,
help_text="Date & time when this record was added in the database.",
)
record_updated = models.DateTimeField(
auto_now=True,
help_text="Date & time when this record was updated in the database.",
)