From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: Guyren Howe <guyren@gmail.com>, Erik Wienhold <ewie@ewie.name>,
        PG-General Mailing List <pgsql-general@postgresql.org>
Subject: Re: nth_value out of more than n values returns null
In-reply-to: <CAKFQuwZH2C=itB7h=S+0UiG5k=U4PsdaLKxsWenZ7GWFdAsDxA@mail.gmail.com>
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>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Mon, 04 Nov 2024 16:44:24 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <2936684.1730767792.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 04 Nov 2024 19:49:52 -0500
Message-ID: <2936685.1730767792@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/2936685.1730767792%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> So just use =E2=80=9Coffset 5_000_000 limit 1=E2=80=9D.  Bringing in a w=
indow function here
> seems unhelpful.

Yeah, that.  A bite-size example might help clarify what the window
function is doing:

regression=3D# create table zed(f1 int) ;
CREATE TABLE
regression=3D# insert into zed select generate_series(1, 10);
INSERT 0 10
regression=3D# select f1, nth_value(f1, 5) over (order by f1) from zed;
 f1 | nth_value =

----+-----------
  1 |          =

  2 |          =

  3 |          =

  4 |          =

  5 |         5
  6 |         5
  7 |         5
  8 |         5
  9 |         5
 10 |         5
(10 rows)

For the first four rows, the window frame doesn't include the row
you want, so you get NULL.  You can fix that with a non-default
window frame:

regression=3D# select f1, nth_value(f1, 5) over (order by f1 ROWS BETWEEN =
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) from zed;
 f1 | nth_value =

----+-----------
  1 |         5
  2 |         5
  3 |         5
  4 |         5
  5 |         5
  6 |         5
  7 |         5
  8 |         5
  9 |         5
 10 |         5
(10 rows)

So yeah, you can get the fifth (or five million'th) row this way, but
you'll get N copies of it, which I assume is not what you want.
Better

regression=3D# select f1 from zed order by f1 offset 4 limit 1;
 f1 =

----
  5
(1 row)

which gets you just the one row and is a lot cheaper too.

			regards, tom lane