Date: Tue, 5 Nov 2024 00:36:24 +0100
From: Erik Wienhold <ewie@ewie.name>
To: Guyren Howe <guyren@gmail.com>
Cc: PG-General Mailing List <pgsql-general@postgresql.org>
Subject: Re: nth_value out of more than n values returns null
Message-ID: <56c4c567-8422-4944-81d1-2a3c2ac5c8fa@ewie.name>
References: <3ed0759d-c332-4f96-a147-499a694e9204@Spark>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <3ed0759d-c332-4f96-a147-499a694e9204@Spark>
Archived-At: <https://www.postgresql.org/message-id/56c4c567-8422-4944-81d1-2a3c2ac5c8fa%40ewie.name>
Precedence: bulk

On 2024-11-05 00:17 +0100, Guyren Howe wrote:
> This query:
> 
> SELECT NTH_VALUE(id, 5000000) OVER (ORDER BY created_at, id ASC) FROM table
> 
> in a table where SELECT COUNT(*) returns a value a few thousand over 5
> million, where id is the primary key, returns null.
> 
> The inclusion of the primary key should make the order by a total
> order. So there should be a 5 millionth row.
> 
> How can this happen?

https://www.postgresql.org/docs/current/functions-window.html explains
it:

    Note that first_value, last_value, and nth_value consider only the rows
    within the "window frame", which by default contains the rows from the
    start of the partition through the last peer of the current row. This is
    likely to give unhelpful results for last_value and sometimes also
    nth_value. You can redefine the frame by adding a suitable frame
    specification (RANGE, ROWS or GROUPS) to the OVER clause. See Section
    4.2.8 for more information about frame specifications.

You probably want to extend the window frame with this:

    SELECT NTH_VALUE(id, 5000000) OVER (
        ORDER BY created_at, id ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) FROM table

-- 
Erik