GROUP BY and Select MAX from each group in Django, 2 queries

gistfile1.txt

'''
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)

Thank you Michel. Nice solution if PostGres isn't an option.

Thanks a lot. This worked for sqlite where distinct isn't an option.

Any Idea...???

class AllLogout(models.Model):

user = models.ForeignKey(Account,on_delete= models.CASCADE)
login_time = models.DateTimeField(null=True,)
logout_time = models.DateTimeField(null=True)


def __str__(self):
    return str(self.user) + ': ' + str(self.logout_time)

this is my model, i want output like this
one day user login&logout multiple times but i need day1 user login,logout time again day 2 same user login, logout time
i tried with this but not working

data1 = Account.objects.filter(Role='Agent').values_list('id', flat=True)

res = []

for agentname in data1:
agentdata = AllLogout.objects.filter(user_id=agentname).order_by('login_time').distinct()
res.append(agentdata1)

output like this:
agent1 login 2021-10-20 17:09:50.957295 logout 2021-10-21 21:45:54.955938
agent 3 login 2021-10-20 12:57:57.923346 logout 2021-10-20 12:58:20.246713
day 2
agent1 login 2021-10-21 12:58:40.147530 logout 2021-10-21 12:59:02.528687
agent 3 login 2021-10-21 21:45:54.858950 logout 2021-10-21 14:01:22.189149