Consider the following example:

from django.db import models

class Book(models.Model):
    title = models.CharField(max_length=100)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)
    pubdate = models.DateField()

Let’s say you only want to query all books, but want every author to only be listed once and show the latest book of that author. There are various ways of going about this (for example, if you are using PostgreSQL you can use DISTINCT ON), but for a Django 2.2 project where I’m using MySQL I tried to use the following Subquery expression:

# Find all books by the same author
books_same_author = Book.objects.filter(
    author=OuterRef('author')
).order_by('-pubdate')

# Slicing the subquery with [1:] should leave out latest book
Book.objects.exclude(
    id__in=Subquery(books_same_author.values('id')[1:]))

However, executing this query raises an exception if you use MySQL 5.7 (haven’t tested on later versions):

django.db.utils.NotSupportedError: (1235, "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'")

The problem is that you cannot use id__in in combination with a sliced subquery .values('id')[:1]). You can workaround this by instead of excluding the “duplicates”, to annotate the latest book of that author and filtering on that value with an F() expression:

Book.objects.annotate(
    latest_by_author=Subquery(books_same_author('id')[:1])
).filter(id=F('latest_by_author'))