public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Guyren Howe <[email protected]>
To: PG-General Mailing List <[email protected]>
Subject: Re: nth_value out of more than n values returns null
Date: Mon, 4 Nov 2024 15:36:17 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <3ed0759d-c332-4f96-a147-499a694e9204@Spark>
References: <3ed0759d-c332-4f96-a147-499a694e9204@Spark>

On 11/4/24 15:17, 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?

If I where to hazard a guess it has to do with this:

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

"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."

Further I am pretty sure that this ORDER BY created_at, id ASC is 
creating a window frame over created_at, id and that there are duplicate 
created_at values which means the frame has less then 5000000 rows. Try 
a smaller number and see what happens.

-- 
Adrian Klaver
[email protected]

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: nth_value out of more than n values returns null
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox