How to get latest unique entries from sqlite db wi

Question

I have a SQLite db which looks like this:

|ID|DateTime|Lang|Details|
|1 |16 Oct  | GB |  GB1  |
|2 |15 Oct  | GB |  GB2  |
|3 |17 Oct  | ES |  ES1  |
|4 |13 Oct  | ES |  ES2  |
|5 |15 Oct  | ES |  ES3  |
|6 |10 Oct  | CH |  CH1  |

I need a Django query to select this:

|1 |16 Oct  | GB | GB1   | 2 |
|3 |17 Oct  | ES | ES1   | 3 |
|6 |10 Oct  | CH | CH1   | 1 |

So this is unique (by Lang) latest (by DateTime) entries with the number of occurrences (by Lang). Is it possible to do this with a single SQL or Django-ORM query?

Answer 1

As you want distinct entries by "Lang" and latest entries by "DateTime", below query will help you,

queryset = Model.objects.distinct("Lang").order_by("-DateTime")

Answer 2

You can use Django annotate() and value() together: link.

when a values() clause is used to constrain the columns that are returned in the result set, the method for evaluating annotations is slightly different. Instead of returning an annotated result for each result in the original QuerySet, the original results are grouped according to the unique combinations of the fields specified in the values() clause. An annotation is then provided for each unique group; the annotation is computed over all members of the group.

Your ORM query should looks like this:

queryset = Model.objects.values("Lang").annotate(
    max_datetime=Max("DateTime"),
    count=Count("ID")
).values(
    "ID", "max_datetime", "Lang", "Details", "count"
)