How to select row by distinct column and max datetime?

Question

Trying to get the last records I inserted in database based on DateTime and distinct column SocialMedia_ID but I get the following error DISTINCT ON fields is not supported by this database backend when it reaches to the below line:

accountsTwitter = StatsTwitter.objects.all().order_by('DateTime').distinct('SocialMedia_id')

I want to mention that below SQL query is working, and I need to convert it to django.

SELECT *  FROM stats_Twitter WHERE DateTime IN (SELECT MAX(DateTime) FROM stats_Twitter GROUP BY SocialMedia_ID)

Answer 1

you can get the last records

last_record = StatsTwitter.objects.last()

if you want last n records then

last_n_records = StatsTwitter.objects.all().order_by('-DateTime')[:n]

Answer 2

Not sure how your SozialMedia model looks like, but I assume you have one. If that's the case than this is the way to go:

accounts_twitter = SocialMedia.objects.annotate(last_stat=Max('StatsTwitter__Datetime'))

I have not tested it but this should work. please also use that I would not recommend naming a field DateTime because this could lead to conflicts with datetime.datetime