Django ORM: get objects distinct on a column and ordered by another column

Question

I have a Django model which (extremely simplified) looks like this:

class MyModel(models.Model):
    date = models.DateTimeField()
    number = models.PositiveIntegerField()

I'd like to get a list of unique numbers, by reverse chronological order in which they appear.

So for example with this data (dates are ordered here for better legibility):

date         | number
-------------+-------
24/12/2014   | 12
23/12/2014   | 8
22/12/2014   | 8
21/12/2014   | 5
20/12/2014   | 12
19/12/2014   | 5
18/12/2014   | 14

I'd get a list that looks like this:

[12, 8, 5, 14]

Intuitively I'd write the query like this:

MyModel.objects.order_by('-date').distinct('number')

But this is invalid ORM code.

How do I solve this problem?

Note that the table in question contains several millions of rows with potentially a lot of repeated numbers, so I can't solve this problem in a "naive" way (e.g. iterating over rows from MyModel.objects.order_by('-date') and picking up numbers on the fly if they haven't been seen before).

Answer 1

@mipadi's answer works great, but I'd prefer avoiding raw SQL in my codebase.

However his answer made it much easier to figure out a native ORM version, so thanks to him! Here's the ORM version I came up with:

 results = MyModel.objects.values('number').annotate(m=Max('date')).order_by('-m')
 results = [r.number for r in results]

It seems like one should be able to do:

results = MyModel.objects.values('number').annotate(m=Max('date')).order_by('-m').values_list('number', flat=True)

But with Django 1.6, I get a FieldError: Cannot resolve keyword 'm' into field. error when adding the last values_list method. There might be a way around this, but I'm not bothered by the 2 line version enough to spend time figuring it out.