Date: Mon, 4 Nov 2024 15:38:05 -0800
From: Guyren Howe <guyren@gmail.com>
To: Erik Wienhold <ewie@ewie.name>
Cc: PG-General Mailing List <pgsql-general@postgresql.org>
Message-ID: <4e60ede0-86a5-4900-b415-05d68ad75cb1@Spark>
In-Reply-To: <56c4c567-8422-4944-81d1-2a3c2ac5c8fa@ewie.name>
References: <3ed0759d-c332-4f96-a147-499a694e9204@Spark>
 <56c4c567-8422-4944-81d1-2a3c2ac5c8fa@ewie.name>
Subject: Re: nth_value out of more than n values returns null
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="67295b80_484c5e90_11af"
Archived-At: <https://www.postgresql.org/message-id/4e60ede0-86a5-4900-b415-05d68ad75cb1%40Spark>
Precedence: bulk

--67295b80_484c5e90_11af
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

I=E2=80=99m trying to get the id of the 5,000,000th record, so I can join=
 against it to get a name. I didn=E2=80=99t fully understand what the doc=
s say there. What am I missing=3F
On 4 Nov 2024 at 15:36 -0800, Erik Wienhold <ewie=40ewie.name>, wrote:
> On 2024-11-05 00:17 +0100, Guyren Howe wrote:
> > This query:
> >
> > SELECT NTH=5FVALUE(id, 5000000) OVER (ORDER BY created=5Fat, id ASC) =
=46ROM 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=3F
>
> https://www.postgresql.org/docs/current/functions-window.html explains
> it:
>
> Note that first=5Fvalue, last=5Fvalue, and nth=5Fvalue consider only th=
e rows
> within the =22window frame=22, which by default contains the rows from =
the
> start of the partition through the last peer of the current row. This i=
s
> likely to give unhelpful results for last=5Fvalue and sometimes also
> nth=5Fvalue. 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=5FVALUE(id, 5000000) OVER (
> ORDER BY created=5Fat, id ASC
> ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED =46OLLOWING
> ) =46ROM table
>
> --
> Erik

--67295b80_484c5e90_11af
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

<html xmlns=3D=22http://www.w3.org/1999/xhtml=22>
<head>
<title></title>
</head>
<body>
<div name=3D=22messageBodySection=22>
<div dir=3D=22auto=22>I=E2=80=99m trying to get the id of the 5,000,000th=
 record, so I can join against it to get a name. I didn=E2=80=99t fully u=
nderstand what the docs say there. What am I missing=3F</div>
</div>
<div name=3D=22messageReplySection=22>On 4 Nov 2024 at 15:36 -0800, Erik =
Wienhold &lt;ewie=40ewie.name&gt;, wrote:<br />
<blockquote type=3D=22cite=22 style=3D=22border-left-color: grey; border-=
left-width: thin; border-left-style: solid; margin: 5px 5px;padding-left:=
 10px;=22>On 2024-11-05 00:17 +0100, Guyren Howe wrote:<br />
<blockquote type=3D=22cite=22>This query:<br />
<br />
SELECT NTH=5FVALUE(id, 5000000) OVER (ORDER BY created=5Fat, id ASC) =46R=
OM table<br />
<br />
in a table where SELECT COUNT(*) returns a value a few thousand over 5<br=
 />
million, where id is the primary key, returns null.<br />
<br />
The inclusion of the primary key should make the order by a total<br />
order. So there should be a 5 millionth row.<br />
<br />
How can this happen=3F<br /></blockquote>
<br />
https://www.postgresql.org/docs/current/functions-window.html explains<br=
 />
it:<br />
<br />
Note that first=5Fvalue, last=5Fvalue, and nth=5Fvalue consider only the =
rows<br />
within the =22window frame=22, which by default contains the rows from th=
e<br />
start of the partition through the last peer of the current row. This is<=
br />
likely to give unhelpful results for last=5Fvalue and sometimes also<br /=
>
nth=5Fvalue. You can redefine the frame by adding a suitable frame<br />
specification (RANGE, ROWS or GROUPS) to the OVER clause. See Section<br =
/>
4.2.8 for more information about frame specifications.<br />
<br />
You probably want to extend the window frame with this:<br />
<br />
SELECT NTH=5FVALUE(id, 5000000) OVER (<br />
ORDER BY created=5Fat, id ASC<br />
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED =46OLLOWING<br />
) =46ROM table<br />
<br />
--<br />
Erik<br /></blockquote>
</div>
</body>
</html>

--67295b80_484c5e90_11af--