MIME-Version: 1.0
In-Reply-To: <34536f21-801c-41cb-a5ba-5389ce8201d0@Spark>
References: <3ed0759d-c332-4f96-a147-499a694e9204@Spark> <56c4c567-8422-4944-81d1-2a3c2ac5c8fa@ewie.name>
 <4e60ede0-86a5-4900-b415-05d68ad75cb1@Spark> <CAKFQuwZH2C=itB7h=S+0UiG5k=U4PsdaLKxsWenZ7GWFdAsDxA@mail.gmail.com>
 <34536f21-801c-41cb-a5ba-5389ce8201d0@Spark>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Mon, 4 Nov 2024 16:53:30 -0700
Message-ID: <CAKFQuwYPU+j46pHakraap1d4jYzPVMx00o95NMe0WLuamkJp+g@mail.gmail.com>
Subject: Re: nth_value out of more than n values returns null
To: Guyren Howe <guyren@gmail.com>
Cc: Erik Wienhold <ewie@ewie.name>, PG-General Mailing List <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000e9ab6d06261efd97"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwYPU%2Bj46pHakraap1d4jYzPVMx00o95NMe0WLuamkJp%2Bg%40mail.gmail.com>
Precedence: bulk

--000000000000e9ab6d06261efd97
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Monday, November 4, 2024, Guyren Howe <guyren@gmail.com> wrote:

> Wouldn=E2=80=99t it be offset 4_999_999?
>

Probably.  I tend to expect off-by-one for these kinds of things and test
my way out.


>
> I=E2=80=99d still like to understand why nth_value doesn=E2=80=99t work.
>

When you perform an order by in a window clause the frame you get by
default ends at the current row.  Consider =E2=80=9Ccount(*) over ()=E2=80=
=9D versus
=E2=80=9Ccount(*) over (order by id)=E2=80=9D.

You need to not use defaults for the window frame if this doesn=E2=80=99t s=
uit you.

David J.

--000000000000e9ab6d06261efd97
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Monday, November 4, 2024, Guyren Howe &lt;<a href=3D"mailto:guyren@gmail=
.com">guyren@gmail.com</a>&gt; wrote:<br><blockquote class=3D"gmail_quote" =
style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">


<div>
<div name=3D"messageBodySection">
<div dir=3D"auto">Wouldn=E2=80=99t it be offset 4_999_999?</div></div></div=
></blockquote><div><br></div><div>Probably.=C2=A0 I tend to expect off-by-o=
ne for these kinds of things and test my way out.</div><div>=C2=A0</div><bl=
ockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #=
ccc solid;padding-left:1ex"><div><div name=3D"messageBodySection"><div dir=
=3D"auto">
<br>
I=E2=80=99d still like to understand why nth_value doesn=E2=80=99t work.</d=
iv></div></div></blockquote><div><br></div><div>When you perform an order b=
y in a window clause the frame you get by default ends at the current row.=
=C2=A0 Consider =E2=80=9Ccount(*) over ()=E2=80=9D versus =E2=80=9Ccount(*)=
 over (order by id)=E2=80=9D.</div><div><br></div><div>You need to not use =
defaults for the window frame if this doesn=E2=80=99t suit you.</div><div><=
br></div><div>David J.</div><div><br></div>

--000000000000e9ab6d06261efd97--