Django ORM: Getting rows based on max value of a column

Question

I have a class Marketorders which contains information about single market orders and they are gathered in snapshots of the market (represented by class Snapshot). Each order can appear in more than one snapshot with the latest row of course being the relevant one.

class Marketorders(models.Model):
    id = models.AutoField(primary_key=True)
    snapid = models.IntegerField()
    orderid = models.IntegerField()
    reportedtime = models.DateTimeField(null=True, blank=True)
    ...


class Snapshot(models.Model):
    id = models.IntegerField(primary_key=True)
    ...

What I'm doing is getting all of the orders across several snapshots for processing, but I want to include only the most recent row for each order. In SQL I would simply do:

SELECT m1.* FROM marketorders m1 WHERE reportedtime = (SELECT max(reportedtime)  
FROM marketorders m2 WHERE m2.orderid=m1.orderid);

or better yet with a join:

SELECT m1.* FROM marketorders m1 LEFT JOIN marketorders m2 ON 
m1.orderid=m2.orderid AND m1.reportedtime < m2.reportedtime 
WHERE m2.orderid IS NULL;

However, I just can't figure out how to do this with Django ORM. Is there any way to accomplish this without raw SQL?

EDIT: Just to clarify the problem. Let's say we have the following marketorders (leaving out everything unimportant and using only orderid, reportedtime):

1, 09:00:00
1, 10:00:00
1, 12:00:00
2, 09:00:00
2, 10:00:00

How do I get the following set with the ORM?

1, 12:00:00
2, 10:00:00

Answer 1

If I understood right you need a list of Marketorder objects that contains each Marketorder with highest reportedtime per orderid

Something like this should work (disclaimer: didn't test it directly):

m_orders = Marketorders.objects.filter(id__in=(
    Marketorders.objects
        .values('orderid')
        .annotate(Max('reportedtime'))
        .values_list('id', flat=True)
))

For documentation check:

http://docs.djangoproject.com/en/dev/topics/db/aggregation/

Edit: This should get a single Marketorder with highest reportedtime for a specific orderid

order = (
    Marketorders.objects
        .filter(orderid=the_orderid)
        .filter(reportedtime=(
            Marketorders.objects
                .filter(orderid=the_orderid)
                .aggregate(Max('reportedtime'))
                ['reportedtime__max']
        ))
)