Queryset select latest record for group

django group by
django queryset
django annotate
django queryset to list
django-filter
django length of queryset
django queryset get field value
django filter in list

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.

You can try this:

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

In my case it worked for Django 2.1

GROUP BY and Select MAX from each group in , Fetch the item from each category with the latest pubdate. ''' model_max_set = Model.objects.values('category').annotate(max_pubdate=  SELECT * FROM ( SELECT *,ROW_NUMBER() OVER(PARTITION BY student_name ORDER BY date DESC) RN FROM transactions )T WHERE T.RN = 1. The above query gives the following result set which is similar to the one we got in example 3. LAST RECORD WITH LAST_VALUE. There is a function named LAST_VALUE in SQL Server and other variants of SQL as well. This function gives the last record for each group or a subgroup within a group. EXAMPLE #4: LAST_VALUE

For Django 1.11+ you can also Subquery, so something like this should work:

from django.db.models import Subquery, OuterRef, F

qs = Location.objects.all()

# make a subquery (filter, order, get 'id')
sq = qs.filter(location=OuterRef('location')).order_by('-update_time').values('id')

# use subquery in your query (via annotation + filter)
qs.annotate(latest=Subquery(sq[:1])).filter(id=F('latest'))

Queryset select latest record for group, Fetch the item from each category with the latest pubdate. ''' model_max_set = Model.objects.values('category').annotate(max_pubdate=  Retrieve Last Record in SQL Server Example 2. In this example, we show you how to retrieve the last row in each Group using a subquery.-- Select First Row in each SQL Group By group USE [SQL Tutorial] GO SELECT * FROM ( SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,ROW_NUMBER() OVER ( PARTITION BY [Occupation] ORDER BY [YearlyIncome] DESC ) AS [ROW NUMBER] FROM

You should use,

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

Django, Queryset select latest record for group. django group by django queryset django queryset to list django annotate django length of queryset django-filter Note: If you only need to determine the number of records in the set (and don’t need the actual objects), it’s much more efficient to handle a count at the database level using SQL’s SELECT COUNT(*). Django provides a count() method for precisely this reason. list(). Force evaluation of a QuerySet by calling list() on it. For example:

Best solution I found here: https://gist.github.com/ryanpitts/1304725

'''
given a Model with:

   category    = models.CharField(max_length=32, choices=CATEGORY_CHOICES)
   pubdate     = models.DateTimeField(default=datetime.now)
   <other fields>

Fetch the item from each category with the latest pubdate.

''' 

model_max_set = Model.objects.values('category').annotate(max_pubdate=Max('pubdate')).order_by()

q_statement = Q()
for pair in model_max_set:
    q_statement |= (Q(category__exact=pair['category']) & Q(pubdate=pair['max_pubdate']))

model_set = Model.objects.filter(q_statement)

Understand Group by in Django with SQL, Django Get first and last record from QuerySet. Example#. To get First object: MyModel.objects.first(). 6. How to select some fields only in a queryset? 7. How to do a subquery expression in Django? 8. How to filter a queryset with criteria based on comparing their field values; 9. How to filter FileField without any file? 10. How to perform join operations in django ORM? 11. How to find second largest record using Django ORM ? 12.

How to Use Grouping Sets in Django, Counting rows is so common that Django includes a function for it right on the QuerySet. Unlike  When we group by country to get the sum of the population, we lost the details of the cities (at least in the query result). Sometimes it makes sense to have more than one value in the values() clause. For example if our database was composed by City / State / Country. Then we could group by using .values('state__name', 'country__name'). This

WHERE mark=(select max(mark) from student), If you need to perform GROUP BY in Django ORM check out Understand The next query executed by the dashboard was used to produce the summary line: 3 Walmart | D3 | 2 | 37.0000000000000000 | 1 (6 rows) We need to find a way to modify a given Django QuerySet, and add grouping sets to it. 11. How to find second largest record using Django ORM ? 12. Find rows which have duplicate field values; 13. How to find distinct field values from queryset? 14. How to use Q objects for complex queries? 15. How to group records in Django ORM? 16. How to efficiently select a random object from a model? 17. How to use arbitrary database

Querying, Does anyone have a QuerySet for that? ( BTW please don't st=Student.objects.​filter(marks__in=Student.objects.all().aggregate(Max('marks'))) --ankit This would select only one row; there can possibly be multiple students with a top note​. In order to do that, i.e. to select all blogs that do not contain entries published with “Lennon” that were published in 2008, you need to make two queries: Blog . objects . exclude ( entry__in = Entry . objects . filter ( headline__contains = 'Lennon' , pub_date__year = 2008 , ), )

Comments
  • That would just return the max update_time of the entire table? I want the max update_time for each location group.
  • "I would like to select the row with the latest update_time for each location."
  • I see. Please check my updated answer. See if it works for all your cases.
  • Thanks, that's the part about Oracle not supporting distinct on fields. Error is: 'DISTINCT ON fields is not supported by this database backend'. :-/