Queryset select latest record for group

Question

Using Django 1.65 Python 3.4.1 Oracle db

Table in the db 'Locations':

  location  | update_time     |  num_01   | num_02 | num_03 |
 -----------+-----------------+-----------+--------+--------
  B         | 06 Feb 18 04:14 |  42       | 43     |   55       
  C         | 22 Feb 17 04:14 |  77       | 99     |   23   
  A         | 05 Feb 18 04:14 |  48       | 43     |   21   
  A         | 01 Feb 18 04:14 |  82       | 83     |   74   

I would like to select the row with the latest update_time for each location.

The results for the above table should be:

  location  | update_time     |  num_01   | num_02 | num_03 |
 -----------+-----------------+-----------+--------+--------
  A         | 05 Feb 18 04:14 |  48       | 43     |   21   
  B         | 06 Feb 18 04:14 |  42       | 43     |   55       
  C         | 22 Feb 17 04:14 |  77       | 99     |   23   

I can use a queryset to return the latest update time for each location:

latest_updates = Locations.objects.values('location').annotate(max_date=Max('update_time')).order_by('location')

but this only returns the location and max update_time when I'm looking for the entire row - num_01, num_02, num_03.

I've spent a lot of time searching stackoverflow but nothing quite fits. Oracle doesn't seem to support a sort by and distinct option that I can get to work.

I'm unable to import Subquery for some reason so this isn't an option for me, and I'm stuck with this version of django etc as this is at work.

The table will eventually have a reasonable amount of data in it so I'm looking for a reasonably efficient solution if possible.

Answer 1

You can try this:

Locations.objects.order_by('location', '-update_time').distinct('location')

In my case it worked for Django 2.1