How can I reference one window function’s result as the order_by parameter in a subsequent window function in Django?

Hi folks,

I’m working on a complex query that chains window functions, where I want to use a column generated by one window function as the order_by input for another. Unfortunately, my current approach results in invalid SQL.

Here’s a simplified version of the query:

PYTHONCopied
Thing.objects
     .annotate(row_number=Window(RowNumber()))
     .annotate(row_number2=Window(RowNumber(), order_by=["row_number"]))
     .filter(row_number2=1)

This fails on PostgreSQL with:

PLAINTEXTCopied
django.db.utils.ProgrammingError: window functions are not allowed in window definitions
LINE 1: ...R () AS "row_number", ROW_NUMBER() OVER (ORDER BY ROW_NUMBER...

(It also fails on SQLite with a less meaningful error message.)

I understand why the generated SQL is not valid:

SQLCopied
SELECT *
FROM (
    SELECT my_app_thing.id                                   AS col1,
           ROW_NUMBER() OVER ()                              AS row_number,
           ROW_NUMBER() OVER (ORDER BY ROW_NUMBER() OVER ()) AS row_number2
      FROM my_app_thing
) qualify
WHERE row_number2 = 1;

However, I’m looking for a way to force the ORM to nest the subqueries so that the output of one window function can be referenced by another, like this:

SQLCopied
SELECT    col1 as id,
          row_number,
          row_number2
FROM (
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY row_number) AS row_number2
           FROM (
                SELECT my_app_thing.id      AS col1,
                       ROW_NUMBER() OVER () AS row_number
                FROM my_app_thing
           ) qualify
) qualify2
WHERE row_number2 = 1;

Is this a missing feature in the ORM, or is it a bug? I expected the ORM to automatically generate the nested subquery when filtering on a column computed by a window function.

Thanks for any insights!